Create Table?

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

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

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

"DFS" <nospam@dfs_.comwrote in message
news:49Nsg.89047$qd2.45685@bignews6.bellsouth.net. ..>
But why bother? A normalised system would have the names stored
discreetly and concatenated at runtime in a query.
>
Keith.
www.keithwilby.com
>

But with 4,000,000 records a query runs really slow... that's why I want a
new table with a permenant field...

"DFS" <nospam@dfs_.comwrote in message
news:49Nsg.89047$qd2.45685@bignews6.bellsouth.net. ..>
But why bother?

A normalised system would have the names stored
discreetly and concatenated at runtime in a query.

Keith.
www.keithwilby.com

DFS wrote:>
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.

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.d...07ea4bc514ad10
>
where Arvin writes:
>
"Try:
>
=[Street1] & (", " + [Street2]) & (", " + [Town]) & (", " + [County])
& (",
" + [PostCode])
>
The "+" operator will not concatenate with a null, while the "&"
will."

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.

Keith Wilby wrote:>
Because he asked?
>

"DFS" <nospam@dfs_.comwrote in message
news:Xd0tg.89218$qd2.24343@bignews6.bellsouth.net. ..>
Let's hope no-one asks you how to boil their head ;-)

Keith Wilby wrote:>
First you peel off the skin...
>
>
>

GCM wrote:>
Runs really slow for what?

>
Do you load a form with all 4 000 000 records?

>
Do you print a letter to them all?

>
E-mail ...?

>
Who are the four million people whose names you have?

>
What happens to the size of your database when you create this new
table?

>
It's certainly true that something would run slowly for these four
million records: the make table query "SELECT INTO ... " How would you
keep this new table current? When one person changes his/her name, when
you find one spelling error do you delete and then recreate this Table?
When one person is added to the list ... ?

>
When you want a subsetof these names, say all the "Howards" will it be
slower to filter out the other records when LastName is one field or
when LastName must be extracted from the entire name field?

>

Hi Lyle,
>
To answer your questions...
>
"Lyle Fairfield" <lylefairfield@aim.comwrote in message
news:1152678168.979037.181270@s13g2000cwa.googlegr oups.com...The searches run really fast, but any query takes a few minutes to run.Yes, and it works well.No, its for information look-ups only... no changes or additions by the
user.NoIt is a death record index for an entire state to be used by genealogists,
school class searches, lawyers, bondsmen and etc. It has already been in use
for a couple of years and would be much easier to use with the names
combined in a string by using a "Start of Field" search.Good question. It jumped from 979,848kb to 1,346,052kb, but when I delete
the old LName, FName, MName fields and then delete the old table (no longer
needed) I'm sure the size will drop, perhaps even lower than the original.Again, there will be no updates, ever.You can run a filter select all Howards or selecting all Howards in a
certain county in only a second or two. Very fast. There are only 6 fields
per record.