Solved

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

Posted on 2011-09-22
25
407 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:sherman6789
  • 10
  • 10
  • 3
  • +2
25 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Better to separate the names into two separate Name fields

Then concatenate them together with a comma:

 LastName & ", " & FirstName
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 75

Expert Comment

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

mx
0
 
LVL 31

Expert Comment

by:Paul Sauvé
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 

Author Comment

by:sherman6789
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 

Author Comment

by:sherman6789
Comment Utility
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
 

Author Comment

by:sherman6789
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Alrighty then ....
The solution for that is @ http:#a36585026
0
 

Author Comment

by:sherman6789
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 

Author Comment

by:sherman6789
Comment Utility
I'm almost there.  How and where do I create an Update query - (see image)?  I am still learning.
(Smile)
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
What version of Access are you using ?

mx
0
 

Author Comment

by:sherman6789
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Sure.  What is your Table's actual Name?  And what is the Actual Name of your 'fullname' field?
0
 

Author Comment

by:sherman6789
Comment Utility
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
Comment Utility
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
 

Author Comment

by:sherman6789
Comment Utility
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
 

Author Comment

by:sherman6789
Comment Utility
Thanks DatabaseMX,  The screen shots helped a lot.  I would not be surprised if others can use this information for their databases.
0
 

Author Closing Comment

by:sherman6789
Comment Utility
I can use this information on many of the databases that we use in the office. Thanks very, very much.
WS
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
You are welcome.

mx
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now