Concatenating two fields in Make-Table query

[Guest]

I have a table that has the fields FIRST NAME and LAST NAME, and would like
to use a Make-Table query that creates a new table that has all the fields
contained this table plus a new field called FULL NAME. I have attempted to
do this by creating the query and placing the following expression in the
Criteria cell of the new field called FULL NAME.
FULL NAME: [FIRST NAME]&" "&[LAST NAME]
The Make-Table query appears to run successfully but no new table is created.
What am I doing wrong? Thanks in advance.

 

[Rick B]

Why?

You are trying to store redundant data. When you need the full name, simply
build it in your query, form, report, etc. It is not normal database design
to store redundant information. What happens if someone gets married? You
really think it is efficient to correct the name in two places instead of
one? By doing this, you are putting the onus on the one maintaining the
data, instead of the person designing the forms and reports.


Rick B

 

[Edward G]

Here is how I would do this:
Create a query with FirstName and LastName fields.
In the first open column in the QBE grid in the row called FIELD enter:
FullName: [FirstName] & " " & [LastName]
Next, click on the word Query on the Menu bar at the top of the window and
select Make Table.
A window will open requesting a name for the new table. Supply one. Click
OK.
Now, on the Toolbar, look for an exclamation point. Hovering the mouse
pointer over this button reveal the
word RUN. Click this button. Confirm in the confirmation window.
Now look for your table.

 

[John Vinson]

On Mon, 20 Dec 2004 07:23:02 -0800, "Jim Hank" <Jim

I have a table that has the fields FIRST NAME and LAST NAME, and would like
to use a Make-Table query that creates a new table that has all the fields
contained this table plus a new field called FULL NAME. I have attempted to
do this by creating the query and placing the following expression in the
Criteria cell of the new field called FULL NAME.
FULL NAME: [FIRST NAME]&" "&[LAST NAME]
The Make-Table query appears to run successfully but no new table is created.
What am I doing wrong? Thanks in advance.

Edward's answered your question correctly; you need to put the
fullname in a vacant Field cell rather than in a Criteria cell.

But the more important question is - *should this new table even
EXIST*? I would say no, it should not!

You may be making the common assumption that you must have data in a
Table in order to report it. That assumption is simply *wrong*. Create
a default Select query with the expression you used in a vacant Field
cell; this query can then be used as the basis for a Form, for a
Report, for exporting or mailmerging, or for any other purpose that
one would use a Table. There's no need to make a new table.

John W. Vinson[MVP]

 

[Edward G]

John,

I recently sent out postcards announcing a new phone number to all customers
who have used my services in the last 3 yrs. Rather than sit around printing
and labelling postcards all day, I uploaded my address list to the US Postal
Services
website and composed a simple postcard. The USPS accepts the address list in
several formats including MS Access. But after reviewing their requirements
for the following
two formats (I could not find requirements for Access), I decided to create
a new table with first and last name concatenated rather than
risk a screwup. The following is cut and pasted from the USPS (note the
treatment for name):

Comma Delimited Mail List

NAME,ADDRESS,CITY,STATE,ZIP
Joe Smith,21 Big Road,Manassas,VA,20110
Margie Smith,45 Round Hill,Reston,VA,20191

Tab Delimited Mail List

NAME ADDRESS CITY STATE ZIP
Joe Smith 21 Big Road Manassas VA 20110
Margie Smith 45 Round Hill Reston VA 20191

 

[Rick B]

You would not need to create a new table, jsut a query or a report with the
data you want.

If you revised your table structure everytime you wanted a different look to
your data, then how useful would Access be? You'd spend your whole time
revising your table structure.

 

[Edward G]

Excuse me Rick, but do you think I uploaded my entire database to the US
Postal Services automated mail service website????
No. I created a blank database. Then I imported this newly made table (with
5 unmistakable fields named exactly like the sample info in the USPS'
website) into the blank database. And this is what I uploaded to them.
My original customer table is unchanged. And I believe Jim Hank who began
this thread is probably doing something
similar. If he had been asking for a way to run an Update query that changes
his original table, I would have offered him advice similar to yours. But
since he was making a NEW table, I figured he did not require a lecture.

Ed

 

[Rick B]

Again, there is no nedd for a new database or a new table. Create a query
to get the results you want, then export them.

No need to do all the steps you mentioned. You are creating a lot more work
than needed.


Rick B

 

[Edward G]

Well, yes Rick, you are right. If I wanted to upload an Excel file to the
USPS, your suggestion is
perfectly valid and would require fewer steps. Still, no harm was done to my
table structure by running
a Make Table query, which was what you were objecting to in previous posts.

Ed

 

[Rick B]

Typically, you would not create a redundant table. Just using normalized
database structure and procedures. If you choose not to, that is fine, but
not something I would want to recommend to other users.

 

[Guest]

Thanks. the suggestions from Edward and John worked just fine.
By the way, I neglected to explain why I needed a new table. The original
table was imported from an extremely large database that includes thousands
of prospective customers, and the names in this DB included first and last
names.
I need to import this information into Outlook 2003's Business Contact
Manager and it has both first/last names as well as full names for each
contact. Now that I have a table that contains both name formats, I can
import this information into the address book of Business Contact Manager and
can use it for our direct marketing programs. These programs use the mail
merge capabilities of both Word and Publisher.
Thank again for the help.

 

[John Vinson]

I need to import this information into Outlook 2003's Business Contact
Manager and it has both first/last names as well as full names for each
contact. Now that I have a table that contains both name formats, I can
import this information into the address book of Business Contact Manager and
can use it for our direct marketing programs.

Not to commit necroequuiflaggelation, but again... you can export a
Table to Outlook 2003's Business Contact Manager; or you can export a
Query, and save a step.

John W. Vinson[MVP]

 

[Edward G]

Jim,

Glad it worked out for you. Thanks for stopping back to let us know.

Ed

 

[Edward G]

As someone who majored in equestrian scatology I'd like to thank you for
that.

Ed