Create Table?

[GCM]

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

[DFS]

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 ...

[Keith Wilby]

"DFS" <nospam@dfs_.comwrote in message
news:49*******************@bignews6.bellsouth.net. ..

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 ...

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

Keith.
www.keithwilby.com

[Lyle Fairfield]

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. 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.

[GCM]

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

Thanks for your help,
GCM
"Keith Wilby" <he**@there.comwrote in message
news:44********@glkas0286.greenlnk.net...

"DFS" <nospam@dfs_.comwrote in message
news:49*******************@bignews6.bellsouth.net. ..

>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 ...

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

Keith.
www.keithwilby.com

[Lyle Fairfield]

GCM wrote:

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

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?

[DFS]

Keith Wilby wrote:

"DFS" <nospam@dfs_.comwrote in message
news:49*******************@bignews6.bellsouth.net. ..

>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 ...

But why bother?

Because he asked?

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

Keith.
www.keithwilby.com

[DFS]

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.d...07ea4bc514ad10

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...

[Keith Wilby]

"DFS" <nospam@dfs_.comwrote in message
news:Xd*******************@bignews6.bellsouth.net. ..

Keith Wilby wrote:

>>
But why bother?

Because he asked?

Let's hope no-one asks you how to boil their head ;-)

Keith.

[DFS]

Keith Wilby wrote:

"DFS" <nospam@dfs_.comwrote in message
news:Xd*******************@bignews6.bellsouth.net. ..

>Keith Wilby wrote:

>>>
But why bother?

Because he asked?

Let's hope no-one asks you how to boil their head ;-)

First you peel off the skin...

[Keith Wilby]

"DFS" <nospam@dfs_.comwrote in message
news:f2******************@bignews6.bellsouth.net.. .

Keith Wilby wrote:

>"DFS" <nospam@dfs_.comwrote in message
news:Xd*******************@bignews6.bellsouth.net ...

>>Keith Wilby wrote:

But why bother?

Because he asked?

Let's hope no-one asks you how to boil their head ;-)

First you peel off the skin...

Shouldn't it come off as part of the boiling process, similar to a tomato?

[GCM]

Hi Lyle,

To answer your questions...

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...

GCM wrote:

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

Runs really slow for what?

The searches run really fast, but any query takes a few minutes to run.

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

Yes, and it works well.

>
Do you print a letter to them all?

No, its for information look-ups only... no changes or additions by the
user.

>
E-mail ...?

No

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

It 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.

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

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.

>
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 ... ?

Again, there will be no updates, ever.

>
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?

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.

>

[Lyle Fairfield]

GCM wrote:

Hi Lyle,

To answer your questions...

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...

GCM wrote:

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

Runs really slow for what?

The searches run really fast, but any query takes a few minutes to run.

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

Yes, and it works well.

Do you print a letter to them all?

No, its for information look-ups only... no changes or additions by the
user.

E-mail ...?

No

Who are the four million people whose names you have?

It 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.

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

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.

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 ... ?

Again, there will be no updates, ever.

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?

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.

Your reasons sound good to me and I have done something similar with
data which was regulation bound not to change except on one date each
year. As the calculations to create the data were costly in terms of
time and resources it was efficient to create an annual table which
could be referenced but not changed.

I think you will need to address the issue of null fields and double
spaces in your table creating SQL. A simple solution that may handle
any cases of the double space is to enclose your function as follows.
Replace(Your function, " ", " ")
assuming your are using Access >=2000

or to run a second query updating FullName to
Replace(FullName, " ", " ")