Separating Names using an Update Query

[Guest]

I am having to import demographic records where the first and last names are
in one field. I know how to separate the first and last names by using a
search and replace operation to insert commas, then exporting the records to
a text file and re-importing them, but is there a way to automate this
procedure by using an update query or some other less clunky means?

 

[fredg]

I am having to import demographic records where the first and last names are
in one field. I know how to separate the first and last names by using a
search and replace operation to insert commas, then exporting the records to
a text file and re-importing them, but is there a way to automate this
procedure by using an update query or some other less clunky means?

It depends upon how the full name field is formatted, i.e. FirstName
LastName, or LastName, FirstName, or LastName, FirstName Initial,
etc., and all records need to be in the same order.

For example, if all the records are like John Smith then you can use
the space to parse the field:
*** Back up your data first ***
Update YourTable Set YourTable.FirstName =
Left([FullName],InStr([FullName]," ")-1), YourTable.LastName] =
Mid([FullName],InStr([FullName]," ")+1);

The above will fail is some records are like John G. Smith, or like
John Herbert Smith, or like Smith, John or like Smith, John H. etc.

If the comma is the separator, just substitute InStr([FullName],",")
above, and reverse the FirstName and LastName field names.

 

[Guest]

Great, that works. Thanks!
--
Tom Parker
Musician and Reports Consultant

I am having to import demographic records where the first and last names are
in one field. I know how to separate the first and last names by using a
search and replace operation to insert commas, then exporting the records to
a text file and re-importing them, but is there a way to automate this
procedure by using an update query or some other less clunky means?

It depends upon how the full name field is formatted, i.e. FirstName
LastName, or LastName, FirstName, or LastName, FirstName Initial,
etc., and all records need to be in the same order.

For example, if all the records are like John Smith then you can use
the space to parse the field:
*** Back up your data first ***
Update YourTable Set YourTable.FirstName =
Left([FullName],InStr([FullName]," ")-1), YourTable.LastName] =
Mid([FullName],InStr([FullName]," ")+1);

The above will fail is some records are like John G. Smith, or like
John Herbert Smith, or like Smith, John or like Smith, John H. etc.

If the comma is the separator, just substitute InStr([FullName],",")
above, and reverse the FirstName and LastName field names.

 

[John Vinson]

I am having to import demographic records where the first and last names are
in one field. I know how to separate the first and last names by using a
search and replace operation to insert commas, then exporting the records to
a text file and re-importing them, but is there a way to automate this
procedure by using an update query or some other less clunky means?
--

Yes... but with some limitations. If you have a field FullName with
(say) "Tom Parker" in it, you can run an Update query updating
FirstName to

Left([FullName], InStr([FullName], " ") - 1)

and LastName to

Trim(Mid([FullName], InStr([FullName], " ")))

The limitations have to do with three word names. My friend Hattie Lou
Beckwith uses "Hattie Lou" as her first name; she'll go into the new
name fields as FirstName "Hattie" and LastName "Lou Beckwith" - both
wrong! On the other hand, a FullName like "Andeas de la Torre" would
be handled correctly; his last name is in fact "de la Torre". Do a
search afterward for

LastName LIKE "* *"

to find all lastnames containing blanks and fix them manually.

John W. Vinson[MVP]

 

[Guest]

John,

Thanks for the information. The data I'm dealing with is like:

BLOW, JOE E.
DOE, JOHN JR.
ROE, JANE

A real mess. I'm working on ways to process it in steps. I've already
worked out a way to copy the suffixes (JR, SR) to a separate column and
delete them.

Tom
--
Tom Parker
Musician and Reports Consultant

I am having to import demographic records where the first and last names are
in one field. I know how to separate the first and last names by using a
search and replace operation to insert commas, then exporting the records to
a text file and re-importing them, but is there a way to automate this
procedure by using an update query or some other less clunky means?
--

Yes... but with some limitations. If you have a field FullName with
(say) "Tom Parker" in it, you can run an Update query updating
FirstName to

Left([FullName], InStr([FullName], " ") - 1)

and LastName to

Trim(Mid([FullName], InStr([FullName], " ")))

The limitations have to do with three word names. My friend Hattie Lou
Beckwith uses "Hattie Lou" as her first name; she'll go into the new
name fields as FirstName "Hattie" and LastName "Lou Beckwith" - both
wrong! On the other hand, a FullName like "Andeas de la Torre" would
be handled correctly; his last name is in fact "de la Torre". Do a
search afterward for

LastName LIKE "* *"

to find all lastnames containing blanks and fix them manually.

John W. Vinson[MVP]

 

[John Vinson]

John,

Thanks for the information. The data I'm dealing with is like:

BLOW, JOE E.
DOE, JOHN JR.
ROE, JANE

A real mess. I'm working on ways to process it in steps. I've already
worked out a way to copy the suffixes (JR, SR) to a separate column and
delete them.

That's actually cleaner than some such data I've seen. You just need
to use the InStr() function to find the comma rather than a blank:
update LastName to

Left([namefield], InStr([namefield], ",") - 1)

and First to

Trim(Mid([Namefield], InStr([namefield], ",") + 1)

This will give you records with

BLOW JOE E.
DOE JOHN JR.
ROE JANE

Note that I had a friend in high school whose legal name was Robert
Junior Barnes. Don't know what his parents were thinking at the time
(his father was NOT named Robert) but Junior was his middle name, not
a (dispensible) title!

John W. Vinson[MVP]