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.
