By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,870 Members | 2,484 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,870 IT Pros & Developers. It's quick & easy.

Last Name, First Name

P: 30
I know that I saw code on this forum to put the Last Name, First Name in the same field when you start with a table that has 2 separate fields (LASTNAME and FIRSTNAME) but I can't find it.

I have a table where the first name and last name are in two separate fields and I want to change the table to put both fields together in a field (MEMBER).

Do I do this using an update query?

Thanks
Mar 13 '08 #1
Share this Question
Share on Google+
9 Replies


Expert Mod 2.5K+
P: 2,545
I know that I saw code on this forum to put the Last Name, First Name in the same field when you start with a table that has 2 separate fields (LASTNAME and FIRSTNAME) but I can't find it.

I have a table where the first name and last name are in two separate fields and I want to change the table to put both fields together in a field (MEMBER).

Do I do this using an update query?

Thanks
Hi. As the answer to the post you refer to said, you do not need to update any field at all, nor do you need to store the combined name in a new field. The combined name is just a calculated field - in this case one which is concatenated from two field values and a string constant.

In the Access graphical query builder add the fields you need from the table, and in a blank column within the grid add a calculated field like this:
Expand|Select|Wrap|Line Numbers
  1. NAME: [LASTNAME] & ", " & [FIRSTNAME]
  2. or
  3. NAME: [FIRSTNAME] & " " & [LASTNAME]
In SQL this is just
Expand|Select|Wrap|Line Numbers
  1. SELECT ([FIRSTNAME] & " " & [LASTNAME]) AS NAME FROM [YourTable];
-Stewart
Mar 13 '08 #2

Scott Price
Expert 100+
P: 1,384
To add to what Stewart has said, having Last Name and First Name in separate columns in your table conforms to proper database design rules which exist for specific reasons. To combine them into one field breaks the rules, which will make you pay later... One example is when you want to sort your table alphabetically by last name... If the names are in one field you are faced with a headache of having to first split the field, sort by the last name, then recombine. With proper design it's far simpler to first sort, then combine on the fly.

Regards,
Scott
Mar 14 '08 #3

P: 30
Thank You for your reply. I'm confused as to where I would use the calculated field. Do I put this in the table or a form where I want to bring the two fields together?

For example:

The ORDER FORM form, where I want the name, address, etc. Do I put the code somewhere in the Name properties?
Thanks
Mar 15 '08 #4

Expert Mod 2.5K+
P: 2,545
Thank You for your reply. I'm confused as to where I would use the calculated field. Do I put this in the table or a form where I want to bring the two fields together?

For example:

The ORDER FORM form, where I want the name, address, etc. Do I put the code somewhere in the Name properties?
Thanks
Hi. My reply referred to a query based on your table, as you can add calculated fields to any query. You do not have to change the design of your table, as Scott also pointed out.

If you want, you can add a calculated field to your form without going through the query step. Open the form in design view, and from the controls toolbox add a new, blank, textbox to your form. In the new control's controlsource property type
Expand|Select|Wrap|Line Numbers
  1. =[Firstname] & " " & [Lastname]
There are many good introductory books on Access which will help you understand the uses of tables, queries and forms. Try out some of the examples you will find in any such book to get a better idea of what Access is about.

-Stewart
Mar 15 '08 #5

P: 30
I went back to my friend's college book on Access and tried a calucated field example and I now know where I went wrong, but I'm using this Name field in a combobox.

In the controlsource of my Name combobox I have =[LastName] & ", " & [FirstName]. That works fine, but it only show me the first record in my table and when I try to select another name it does not change.

The form I am creating is an Order Form which will pull Customer info and Product info in a subform. I will be autofilling the address info which I think I know how to do.

Before I started creating a database with the real info, I played around with dummy info but used the Name field with the first and last name together.

I have the Access for Dummies, the Form & Reports for Dummies and my friend's college book as reference. Believe me when I say, I reread the sections alot before posting a question here...LOL. I appreciate your patience and all the advice you have given me thusfar.

Thanks
Mar 15 '08 #6

Scott Price
Expert 100+
P: 1,384
You can do the same thing in your query. Then tie the text box to the field in the query just as you would any other query field.

Make sure you form's record source is the query.

In a blank field of the query in design view type something like this:
Expand|Select|Wrap|Line Numbers
  1. Name: [FirstName] & " " &  [LastName]
Regards,
Scott
Mar 15 '08 #7

missinglinq
Expert 2.5K+
P: 3,532
One example is when you want to sort your table alphabetically by last name... If the names are in one field you are faced with a headache of having to first split the field, sort by the last name, then recombine. With proper design it's far simpler to first sort, then combine on the fly.
While design best practise obviously dictates having LastName and FirstName in separate fields, for the reasons stated, your example is probably the one case where it is actually easier to do with a single field of LastName FirstName. With a single field like this you simply sort on the one field. With two fields, you actually would have to do a double-sort, first on LastName and then on FirstName. There would be no practical reason to sort on LastName only; the records might be sorted alphabetically and they might not .

Linq ;0)>
Mar 15 '08 #8

P: 30
Thank You. I think I will go back to the combined field in the DB. It is a small database and only 54 records in the MEMBER table. Hopefully, it won't cause me any problems down the road.
Mar 16 '08 #9

P: 2
Thanks all I solve the issues from your method
Mar 5 '17 #10

Post your reply

Sign in to post your reply or Sign up for a free account.