473,320 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Last Name, First Name

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
9 16726
Stewart Ross
2,545 Expert Mod 2GB
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
1,384 Expert 1GB
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
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
Stewart Ross
2,545 Expert Mod 2GB
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
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
1,384 Expert 1GB
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
3,532 Expert 2GB
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
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
Thanks all I solve the issues from your method
Mar 5 '17 #10

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

Similar topics

1
by: PT | last post by:
I got a problem. And thats..... First of all, I got these three tables. ------------------- ------------------ ---------------------- tblPerson tblPersonSoftware ...
0
by: Sascha Folville | last post by:
Hi, I'm trying to transform a XML document to PDF using apache (xerces). I want different formatting for first and last page. My code looks like this: <fo:layout-master-set>...
1
by: Prasad Karunakaran | last post by:
I am using the C# DirectoryEntry class to retrieve the Properties of an user object in the Active Directory. I need to get the First Name and Last Name as properties. I know it is not supported...
5
by: xixi | last post by:
hi, we are running db2 udb on v8.1, i am using jdbc sql, is there anyway to create a sql saying i want to get the last record of resultset? i try to open a big resultset and point the cursor to the...
20
by: Tom Warren | last post by:
Is there a formal name for the (Mc,Mac,O,Van...) part of a last name? Tom
8
by: Jean | last post by:
Hello all, I have the following data, that was queried and sorted to columns PROBLEM_ID and then by STATUSDATE (ascending): STATUS_ID STATUSDATE PROBLEM_ID --------- ---------- ...
32
by: James Curran | last post by:
I'd like to make the following proposal for a new feature for the C# language. I have no connection with the C# team at Microsoft. I'm posting it here to gather input to refine it, in an "open...
1
by: Prasad Karunakaran | last post by:
I am using the C# DirectoryEntry class to retrieve the Properties of an user object in the Active Directory. I need to get the First Name and Last Name as properties. I know it is not supported...
1
by: hbkiwi | last post by:
Hi, Just playing with Access fairly new to it (like days old). Also it's my first post here so if I'm in the wrong place, sorry.. What I'm wanting to know is basically I have lets call it Table...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.