Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"? (1 Viewer)

derekbeck · Mar 2, 2012

Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"?

I have an example database attached.

I have many names in a table called "Personnel". The original designer simply built one field called "Name" in which the format is Last First M. As I am undertaking many new upgrades to this db, I thought it prudent to modify this to separate out the fields, so that I've now added the following fields: "Last Name" "First Name" "Middle Name".

Question 1: is splitting this up a smart idea?

Question 2: is there some simple query code I can use to populate my three new fields, based on the contents of the original "Name" field, the format of which is standardized? What is that query code I would use?

Next, I have a "Task" table with a field "LeadOld", which was just a text field with the lead person for the task. I have created a new field "LeadNew", which is a lookup to the table "Personnel".

Question 3: I would like to run another query here on table "Task" that will update "LeadNew" based on "LeadOld", and I learned how to do this before thanks to this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=222485 but in this case, the items do not match exactly, because LeadOld is the entire name, and LeadNew is just the last name. So how would I modify the query suggested at that other thread for my purposes here?

Question 4: Finally, I would like, in the datasheet view of the "Tasks" table if possible, under the field "LeadNew", I would like it to show the full name if possible, not simply the last name as it does now. How do I do this?

Sorry for the many questions...

Humbly,
Derek

John Big Booty · Mar 2, 2012

Re: Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"?

derekbeck said:
I have an example database attached.

I have many names in a table called "Personnel". The original designer simply built one field called "Name" in which the format is Last First M. As I am undertaking many new upgrades to this db, I thought it prudent to modify this to separate out the fields, so that I've now added the following fields: "Last Name" "First Name" "Middle Name".

Question 1: is splitting this up a smart idea?

...

Definatley

derekbeck said:
...

Question 2: is there some simple query code I can use to populate my three new fields, based on the contents of the original "Name" field, the format of which is standardized? What is that query code I would use?

...

To extract the last name use;

Code:

[URL="http://www.techonthenet.com/access/functions/string/left.php"]Left[/URL]([Name],[URL="http://www.techonthenet.com/access/functions/string/instr.php"]InStr[/URL]([Name]," "))

To extract the First name use;

Code:

[URL="http://www.techonthenet.com/access/functions/string/mid.php"]Mid[/URL]([name],InStr([name]," "),([URL="http://www.techonthenet.com/access/functions/string/instrrev.php"]InStrRev[/URL]([name]," ")-InStr([name]," ")))

and to extract the middle name use;

Code:

[URL="http://www.techonthenet.com/access/functions/string/right.php"]Right[/URL]([name],[URL="http://www.techonthenet.com/access/functions/string/len.php"]Len[/URL]([name])-InStrRev([name]," "))

derekbeck said:
...

Next, I have a "Task" table with a field "LeadOld", which was just a text field with the lead person for the task. I have created a new field "LeadNew", which is a lookup to the table "Personnel".

Question 3: I would like to run another query here on table "Task" that will update "LeadNew" based on "LeadOld", and I learned how to do this before thanks to this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=222485 but in this case, the items do not match exactly, because LeadOld is the entire name, and LeadNew is just the last name. So how would I modify the query suggested at that other thread for my purposes here?

...

You should be able to compare the two by using the criteria above for extracting the last name, that way you can reduce the "LeadOld" to the last name only and compare that with "LeanNew", but I would suggest you assign an autonumber Primary Key (PK) to each employee and store that rather than a name.

derekbeck said:
...
Question 4: Finally, I would like, in the datasheet view of the "Tasks" table if possible, under the field "LeadNew", I would like it to show the full name if possible, not simply the last name as it does now. How do I do this?

Sorry for the many questions...

Humbly,
Derek

You can concatenate your three name fields together at any time you need for display purposes using the following as an expression in a query;

Code:

[LastnameField] & ", " & [FirstNameField] & " " & [MidNameField]

or variations there of.

derekbeck · Mar 2, 2012

Re: Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"?

Awesome, thanks, I'll toy with this and post back on my progress. I will have a primary key in the personnel table. Is there an easy way to write a query to simply do the following:

1) lookup the LeadOld in the Personnel table and determine the primary key and then

2) list in LeadNew the person by lookup, showing their last name + first name, drawing upon those separate two fields?

As I am such a novice at this stuff, any chance you can offer the SQL code to do these two steps? (as you can probably do it off the top of your head)

Thanks again!
Derek

John Big Booty · Mar 2, 2012

Re: Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"?

derekbeck said:
Awesome, thanks, I'll toy with this and post back on my progress. I will have a primary key in the personnel table. Is there an easy way to write a query to simply do the following:

1) lookup the LeadOld in the Personnel table and determine the primary key and then

...

First up create a select query that selects the PersonnelID and also has an expression that uses the criteria to extract the Last name (as shown in my first post), save that query. Now create a second query that uses the query we just create along with the table that holds your now normalised names, you can link the query and the table via the expression in the first query and the last name in the normalised name table and update the PersonnelID.

derekbeck said:
...

2) list in LeadNew the person by lookup, showing their last name + first name, drawing upon those separate two fields?

As I am such a novice at this stuff, any chance you can offer the SQL code to do these two steps? (as you can probably do it off the top of your head)

Thanks again!
Derek

For this I would use a combo box for this. use a query as it's Row Source to collects the PersonnelID and concatenate the three parts of the name together as described in my first post.

Have a look at what I've done in your sample DB. In which I've demonstrated both processes.

derekbeck · Mar 14, 2012

Re: Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"?

Thank you so much for your replies. I've tried to follow along, and decipher your example database, and am sorry to say I only understood about half of it. Access is quite unlike any other computer stuff I've learned, so I'm struggling a bit with it.

I have tried to replicate your steps, to understand what's going on. Maybe you can step me through the rest, one step at a time?

I've attached my updated version, which has 3 queries as you described above, and renders a table in each case, one for the first name, one for the middle, one for the last name.

My next step is: How do I generate, perhaps using an update query, three new fields in the "Personnel" table, one being "First Name", one being "Middle Name", one being "Last Name"? This must be text, not a link to my queries, because ultimately I want to deleted the combined "Name" field and the three temporary queries.

Thanks for your patience and continued help!
Derek

PS: Btw, my queries named these variables, which I hope I did write. For instance, instead of using your Expr1 variable name, I wrote for the first name, this query language:

Code:

SELECT Mid([Name],InStr([Name]," "),(InStrRev([Name]," ")-InStr([Name]," "))) AS [First Name], Personnel.NameID
FROM Personnel;

Not sure if my use of

Code:

[First Name]

as above is proper.

derekbeck · Mar 27, 2012

Re: Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"?

I figured it out, thanks again.

Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"? (1 Viewer)

derekbeck

Registered User.

Attachments

John Big Booty

AWF VIP

derekbeck

Registered User.

John Big Booty

AWF VIP

Attachments

derekbeck

Registered User.

Attachments

derekbeck

Registered User.

Similar threads

Users who are viewing this thread