Re: Create Table?

Lyle Fairfield wrote:
DFS wrote:
GCM wrote:
Hi,

I have a table that has the following fields; LName, FName and
MName. Also there are other fields in the table. I need to create
another table with those fields combined as "Trim([LName] & " " &
[FName] & " " & [MName]). How can I create this table?

Thanks in advance...
GCM


SELECT (Trim(LName) & ' ' & Trim(FName) & ' ' & Trim(MName)) AS
FullName INTO NewTable
FROM ExistingTable
WHERE ...

Years ago SELECT (Trim(LName) & ' ' & Trim(FName) & ' ' & Trim(MName))
might result in two spaces in some cases where a field was null or
held a zero length string.

If someone didn't have a first name...



I don't know if that is the case anymore
but a solution has been discussed many times here as in:

http://groups.google.ca/group/comp.databases.ms-access/msg/a807ea4bc514ad10

where Arvin writes:

"Try:

=[Street1] & (", " + [Street2]) & (", " + [Town]) & (", " + [County])
& (",
" + [PostCode])

The "+" operator will not concatenate with a null, while the "&"
will."

I did not know that. Thanks.





Regardless, I am with Keith in wondering if creatig this table is
REALLY necessaryfor the original poster as the first part of your
query gives all that the new table gives.

There can be any number of scenarios where he needs a discrete table of full
names. Mine is not to question why...



.

Relevant Pages

  • RE: Leading blanks being stripped
    ... FullName - FName, MName, LName ... FormalName - Prefix, FName, MName, LName, Degrees ...
    (microsoft.public.access.forms)
  • SQL UNION Question (or not UNION?)
    ... SELECT DISTINCT Last_Name as LName, First_Name as FName, Middle_Name as ... MName From Table1 UNION SELECT DISTINCT LastName as LName, ... FName, MiddleName as MName From Table2 ...
    (microsoft.public.vb.database)
  • Re: SQL UNION Question (or not UNION?)
    ... MiddleName as MName From Table2 ... SELECT DISTINCT Last_Name as LName, First_Name as FName, Middle_Name ... FirstName as FName, MiddleName as MName, 2 as FromTable From Table2 ...
    (microsoft.public.vb.database)
  • Re: Create Table?
    ... DFS wrote: ... I have a table that has the following fields; LName, FName and MName. ...
    (comp.databases.ms-access)
  • Re: Form wont remember last value
    ... "Ken Snell (MVP)" wrote: ... "I am using the LName and FName parameters as the Control Source for ... Report SQL: ...
    (microsoft.public.access.formscoding)