Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 105 votes, 5.00 average. Display Modes
Old 03-01-2012, 06:54 PM   #1
derekbeck
Newly Registered User
 
Join Date: Feb 2012
Posts: 16
Thanks: 2
Thanked 0 Times in 0 Posts
derekbeck is on a distinguished road
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/...d.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
Attached Files
File Type: accdb Database2.accdb (1.00 MB, 576 views)

derekbeck is offline   Reply With Quote
Old 03-01-2012, 09:35 PM   #2
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"?

Quote:
Originally Posted by derekbeck View Post
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

Quote:
Originally Posted by derekbeck View Post
...

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:
Left([Name],InStr([Name]," "))
To extract the First name use;
Code:
Mid([name],InStr([name]," "),(InStrRev([name]," ")-InStr([name]," ")))
and to extract the middle name use;
Code:
Right([name],Len([name])-InStrRev([name]," "))
Quote:
Originally Posted by derekbeck View Post
...

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/...d.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.

Quote:
Originally Posted by derekbeck View Post
...
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.
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 03-02-2012, 12:27 AM   #3
derekbeck
Newly Registered User
 
Join Date: Feb 2012
Posts: 16
Thanks: 2
Thanked 0 Times in 0 Posts
derekbeck is on a distinguished road
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

derekbeck is offline   Reply With Quote
Old 03-02-2012, 01:33 AM   #4
John Big Booty
AWF VIP
 
John Big Booty's Avatar
 
Join Date: Aug 2005
Location: Planet 10, by way of the Eighth Dimension
Posts: 8,263
Thanks: 113
Thanked 1,012 Times in 865 Posts
John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough John Big Booty is a jewel in the rough
Re: Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"?

Quote:
Originally Posted by derekbeck View Post
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.

Quote:
Originally Posted by derekbeck View Post
...

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.
Attached Files
File Type: zip Database2.zip (52.0 KB, 604 views)
__________________
...but they're only monkey boys!!!
John Big Booty is offline   Reply With Quote
Old 03-14-2012, 02:33 PM   #5
derekbeck
Newly Registered User
 
Join Date: Feb 2012
Posts: 16
Thanks: 2
Thanked 0 Times in 0 Posts
derekbeck is on a distinguished road
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.
Attached Files
File Type: accdb Database2.2.accdb (1.08 MB, 433 views)
derekbeck is offline   Reply With Quote
Old 03-26-2012, 03:23 PM   #6
derekbeck
Newly Registered User
 
Join Date: Feb 2012
Posts: 16
Thanks: 2
Thanked 0 Times in 0 Posts
derekbeck is on a distinguished road
Re: Query to Convert "Name" field to "Last Name" "First Name" "Middle Name"?

I figured it out, thanks again.

derekbeck is offline   Reply With Quote
Reply

Tags
names , update query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
What's the difference between the "Enabled" and "Locked" field properties? RSW Forms 4 11-16-2012 12:29 PM
Qry To Exclude Record If All Fields On Page Is Blank Except Field "xx", "yy" And "zzz vancey Queries 0 03-17-2011 01:43 AM
Defaulting a "Medium Time" field to be "p.m." instead of "a.m."? Cobo Tables 4 04-26-2010 02:37 PM
query criteria - building "In" clause in "hidden" textbox - mult values wware Forms 3 03-11-2007 06:25 AM
One query to return either records within "Date Range" or "12-Month Rolling" Cosmos75 Queries 2 05-26-2005 03:45 AM




All times are GMT -8. The time now is 08:48 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World