Need to convert list of names from "First Name" and "Last Name" to "Last Name" plus comma then "First Name"

We have an access table of about 800 records. The field containing the names of the participants is (First Name + Space + Last Name), I need to change them to (Last Name + Comma + space + First Name).  This needs to be done only once to correct the current database because we will be using a combo box with the names in alphabetical order by last name for all future inputs. Future names will be taken from a new table which will be in the correct order.

How can I let the computer reverse the order of the names and automatically place a "comma" and space between the last and first name for each record?

Where would the command be placed?  We are using MS Access version 7 in Windows.

Any assistance you give will be appreciated.

Thank you.
sherman6789Asked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
How to create the Update query.  Close any open objects, so that just the database window is open.

See images and use the SQL below - which - as you asked - now takes the existing Mgr Name ... and populated a new field - NewGrant_Mgr_Name - with the modified name scenario. So, that new field needs to be in your table.

UPDATE tblGrant_Info SET tblGrant_Info.NewGrant_Mgr_Name = Trim(Mid([Grant_Mgr_Name],InStrRev([Grant_Mgr_Name]," ")+1)) & ", " & Trim(Left([Grant_Mgr_Name],InStrRev([Grant_Mgr_Name]," ")-1))
WHERE (((tblGrant_Info.Grant_Mgr_Name) Is Not Null));


Capture1.gif
Capture2.gif
Capture3.gif
Capture4.gif
Capture5.gif
0
 
Patrick MatthewsCommented:
sherman6789,

With respect, rather than just switching the order, I strongly urge you to instead put the name parts in different fields.  You will be far, far better off in the long run if you atomize this data.

Patrick
0
 
Jeffrey CoachmanMIS LiasonCommented:
Better to separate the names into two separate Name fields

Then concatenate them together with a comma:

 LastName & ", " & FirstName
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Make a BACKUP first, then try this:

UPDATE Table1 SET Table1.FullName = Trim(Mid([FullName],InStr(1,[FullName]," ")+1)) & ", " & Trim(Left([FullName],InStr(1,[FullName]," ")-1))
WHERE (((Table1.FullName) Is Not Null));


mx
0
 
Jeffrey CoachmanMIS LiasonCommented:
Add two fields to your table:
FirstName
LastName

Then run a query like this to separate the names:
UPDATE tblNames SET tblNames.FirstName = Left([Fullname],InStr([FullName]," ")-1), tblNames.LastName = Right([fullName],Len([Fullname])-InStr([FullName]," "));

Then again, use concatenation to "Display" them the way you want:
     LastName & ", " & FirstName

JeffCoachman

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I don't agree that it's *always* the best way. It really depends on how you expect to use the Name.

mx
0
 
Paul SauvéRetiredCommented:
BE CAEFUL - since you are using Access there may be many other tables. forms and reports that may have to be modified if they reference your FirstName_LastName table!

Another potential problem is a name like Anna Maria Smith or John Smith Jones and yuu have to be careful WHERE you break up the name to place the comma.

I think the easiest way to accomplish the final result is to read sequentially through the a copy of the original table and create 2 tables: FirstName and LastName. Then recombine these two tables to get a new file with LastName + "," + FirstName.

Of course, as matthewspatrick says in his answer (36584202) , use two fileds. This is much more elegant and easy to manage. You won't even require the comma.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Paul, you bring up some great additional points

Leaving the names in one field is great, if you know at the time the table is created that you will never be asked to display the names in any other way than the way they are currently stored...

So for that reason, and what you posted, I also agree with Patrick's first post.

;-)

Jeff
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
sherman6789,

Here is a mod to take into account something like Joe Bob Boag

UPDATE Table1 SET Table1.FullName = Trim(Mid([FullName],InStrRev([FullName]," ")+1)) & ", " & Trim(Left([FullName],InStrRev([FullName]," ")-1))
WHERE (((Table1.FullName) Is Not Null));

mx
0
 
sherman6789Author Commented:
Thank you for your responses.  

Originally, I did have a first name and a last name field in a simple table.  The program put them together for the Combo pull down.  When it was only about twelve (12) managers listed there was no problem and I kept my own list for the program.  Since then, the number of Managers has increased to about sixty (60).  The names change frequently.  Also, the department has an Excel spreadsheet that is frequently updated removing the names of people who have left the company and adding new staff members.  The spreadsheet also shows additional information such as location, phone numbers, experience, etc.  I decided that I should be able to use that spreadsheet which is stored on a shared directory and let Access show the current names as a pull down on my program.  The spreadsheet is in alphabetical order by the name field. The names are in one field with the Last name + comma + First name + (X).  The (X) stands for their status and may be (C), (F), (T), (M) or "Blank".  Examples: Smith, David (C); or Henderson, Jackie; or Johnson, Mike (C).  The users will ignore the (X) code when they select from the pull down.

The MS Database being used is MS Access version 7 but because many of the users have version 3 on their computers, I convert and save the database as version 3 so that everyone can open it.  The Excel spreadsheet is version 7.  Microsoft rules will not allow me to pull information from Excel version 7 into Access version 3; therefore I think that I must save a copy of the spreadsheet and convert it into Version 3 for compatibility. (I AM NOT SURE THAT THIS IS TRUE). At this point, I am NOT using Front end and Back end technology yet.  I will in the future. I also expect to take an advanced Access class in the next few months and will make the entire system more efficient.  But for now I must work with what we have.

In any event, that is why I am now using only one field for the name and not two.

I hope that this makes sense. I do appreciate the advice that I am receiving.  I will study them all tonight.

Any other suggestions will be apprecaited.   THANK YOU!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Thank you for the explanation as to why ... you are using just one field :-)

The solution for that is @ http:#a36585026

Of course, there could be ... some anomalies that are not covered, depending exactly on your name data.

mx
0
 
sherman6789Author Commented:
Additional note:

The names are already sorted and everyone uses the spreadsheet for versious reasons.  I don't expect to need to separate the first and last names for things like writing letter, making forms, etc.  I am just too lazy to want to spend much time updating the name list when the department has it updated all of the time and it is on a shared directory.  Thanks again.
0
 
sherman6789Author Commented:
Thanks everyone for the great advice.

I will close this thread very soon.  Remember that this is a one time change.  We willl immediately begin using the pull down from the Department's List for new entries.  I just need to change the records that have already been entered into the system to match the way the new records will be entered. (Last name + comma + space = First Name)  There are no middle names used. Luckily, the possible double name problems are not in any of the names already in the system.   Thanks very much.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Alrighty then ....
The solution for that is @ http:#a36585026
0
 
sherman6789Author Commented:
Thanks DatabaseMX.

Where do I put the commands and how do I use it?

UPDATE Table1 SET Table1.FullName = Trim(Mid([FullName],InStrRev([FullName]," ")+1)) & ", " & Trim(Left([FullName],InStrRev([FullName]," ")-1))
WHERE (((Table1.FullName) Is Not Null));

Do I need to create another field so that I can compare the old and the new?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sure.  Just create an Update query - see image'

"Do I need to create another field so that I can compare the old and the new?"

That's totally up to you. If you do, you could call it NewFullName, then just replace FullName in the SQL above (or your new query) with NewFullName - or whatever you want to call it.

mx
0
 
sherman6789Author Commented:
I'm almost there.  How and where do I create an Update query - (see image)?  I am still learning.
(Smile)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What version of Access are you using ?

mx
0
 
sherman6789Author Commented:
I am using MS Access version 7 but I convert and save it as Version 3 because it is on a shared directory and some of the users have version 3.  By saving it this way, everyone can use it.  Within the next two months, I' expect to take an advanced Access class and will probably begin to use "Front End and Back End" technology and other upgades.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sure.  What is your Table's actual Name?  And what is the Actual Name of your 'fullname' field?
0
 
sherman6789Author Commented:
Thanks DatabaseMX:

MS Access version 7 saved as version 3
DB Name:  OC_Grantee_Matrix
Table:  tblGrant_Info
Query:  qryGrant_Info
Form:  frmEntry
Field:  Grant_Mgr_Name   (Contains all of the OLD Grant Managers' names)
**********************************************
MS Excel  version 7
File Name:  OC_Staff_List
Sheet:  Sheet 1
Field:  A1:A97 (Contains all of the NEW grant managers' names)

NOTE: I was warned that MS Access version 7 will not allow a db file saved as version 3 import information from an Excel File at version 7.  This might mean that I have to convert the Excel file to version 3 for compatibility.  I hope that that is not the case because that will mean another step must be added.

Thanks for your help.
0
 
sherman6789Author Commented:
Sorry for the delay.  This project is on hold until Wednesday (Oct 5, 2011).  Will finish test and close thread then.  The prospects look very, very good.  Thanks DatabaseMX.
0
 
sherman6789Author Commented:
Thanks DatabaseMX,  The screen shots helped a lot.  I would not be surprised if others can use this information for their databases.
0
 
sherman6789Author Commented:
I can use this information on many of the databases that we use in the office. Thanks very, very much.
WS
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You are welcome.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.