If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Switch Last Name First Name to First Name Last Name
Hello,
Access 2003. I have a single field called "Name" in a table called "Members". There is about 300 records in a Last Name First Name format, example Doe John Moore Dave Johnson Jacob Mario (Some names have Middle Names or Sir's) How could I run a query to change the names to First Name Last Name? Or if you recommend in Excel how would I do this? John Doe Dave Moore Jacob Mario Johnson Thanks. Iram |
#2
|
|||
|
|||
Switch Last Name First Name to First Name Last Name
On Thu, 3 Jun 2010 16:35:15 -0700, Iram
wrote: Hello, Access 2003. I have a single field called "Name" in a table called "Members". Ouch. Name is a reserved word - a table has a Name property, a field has a Name property, a form control has a Name property... Access can and will get confused! I would very strongly recommend revising your table structure to have separate fields for the components of the name: FirstName, MiddleName, LastName, Suffix (e.g. Jr., III). There is about 300 records in a Last Name First Name format, example Good that there are not too many, this can be a fair bit of work to handle the exceptions. Doe John Moore Dave Johnson Jacob Mario How about Mary Jo Johnson (first name Mary Jo, just ask her); or Hans ten Broek (first name Hans, last name ten Broek); or Wing Men Li (you'll have to find out whether he's using the Chinese tradition of family name first or has turned it around and is actually a member of the Li family). (Some names have Middle Names or Sir's) Sir Richard Featherstonehaugh Wembley-Fawkes III.... shudder How could I run a query to change the names to First Name Last Name? Or if you recommend in Excel how would I do this? John Doe Dave Moore Jacob Mario Johnson I'd do this in a series of passes. Add the additional fields suggested, or a reasonable variant thereof. First run an Update query: UPDATE table SET FirstName = Left([Name], InStr([Name], " ") -1), LastName = Mid([Name], InStr([Name], " ") + 1) WHERE [Name] LIKE "* *"; THis will parse out all the simple two-word names. With only 300 I'd then just run a query with a criterion LIKE "* *" on LastName to select three- or more-word names; you can manually edit them. Or you can run an analog of the query above to populate middle and last name, and then carefully edit the records. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Switch Last Name First Name to First Name Last Name
Thank you John W. Vinson!
"John W. Vinson" wrote: On Thu, 3 Jun 2010 16:35:15 -0700, Iram wrote: Hello, Access 2003. I have a single field called "Name" in a table called "Members". Ouch. Name is a reserved word - a table has a Name property, a field has a Name property, a form control has a Name property... Access can and will get confused! I would very strongly recommend revising your table structure to have separate fields for the components of the name: FirstName, MiddleName, LastName, Suffix (e.g. Jr., III). There is about 300 records in a Last Name First Name format, example Good that there are not too many, this can be a fair bit of work to handle the exceptions. Doe John Moore Dave Johnson Jacob Mario How about Mary Jo Johnson (first name Mary Jo, just ask her); or Hans ten Broek (first name Hans, last name ten Broek); or Wing Men Li (you'll have to find out whether he's using the Chinese tradition of family name first or has turned it around and is actually a member of the Li family). (Some names have Middle Names or Sir's) Sir Richard Featherstonehaugh Wembley-Fawkes III.... shudder How could I run a query to change the names to First Name Last Name? Or if you recommend in Excel how would I do this? John Doe Dave Moore Jacob Mario Johnson I'd do this in a series of passes. Add the additional fields suggested, or a reasonable variant thereof. First run an Update query: UPDATE table SET FirstName = Left([Name], InStr([Name], " ") -1), LastName = Mid([Name], InStr([Name], " ") + 1) WHERE [Name] LIKE "* *"; THis will parse out all the simple two-word names. With only 300 I'd then just run a query with a criterion LIKE "* *" on LastName to select three- or more-word names; you can manually edit them. Or you can run an analog of the query above to populate middle and last name, and then carefully edit the records. -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|