Last Name, First Name

[mgstlucia]

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

[Stewart Ross]

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:

NAME: [LASTNAME] & ", " & [FIRSTNAME]
or
NAME: [FIRSTNAME] & " " & [LASTNAME]

In SQL this is just
[code=sql]SELECT ([FIRSTNAME] & " " & [LASTNAME]) AS NAME FROM [YourTable];[/code]
-Stewart

[Scott Price]

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

[mgstlucia]

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

[Stewart Ross]

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

=[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

[mgstlucia]

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

[Scott Price]

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:

Name: [FirstName] & " " &  [LastName]

Regards,
Scott

[missinglinq]

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

[mgstlucia]

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.

[Feroz37]

Thanks all I solve the issues from your method