Switch Last Name First Name to First Name Last Name

I

Iram

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
 
J

John W. Vinson

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.... said:
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.
 
I

Iram

Thank you John W. Vinson!



John W. Vinson said:
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.... said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top