Splitting First and Last Name Field into their own Fields

P

PaulaV

I have one field with first, middle and last name. I need to separate all
three into their own fields. HELP!! I'm not Access literate so please walk
me through all the lingo.
 
A

Alex White MCDBA MCSE

Nasty.....

it going to be difficult (or impossible) to do automatically, because how do
you work out if the bits of the field are part of the first name, middle,
surname, how many records have you got?

to do a proper job I use the following

title e.g. mr,mrs.miss/ms/dr
first name
middle
surname
salutation e.g. how you would like to refer to them in say a letter, some
would be

Dear Alex

or

Dear Mr White
 
R

Rick B

Wat about the following:

John D Smith
John Smith
Billy Bob Johnson
Juan San Pedro
Juan Marco San Pedro

You really can't do this easily. Fix your data structure and go back and
fix the records manually. You might try to do SOME of them using update
queries. You could run a query to find all records with only one space.
Then move them into first and last name. You could find all cases where the
middle name is only one character (initial) and sort them into the three
fields. Then, handle whats left manually.

You'll have to play around with the queries to identify the ones you can
using the logic I just mentioned. Or, you might want to just update them
all, then go back and look for exceptions by manually reading the list.

Good Luck.
 
L

Larry Daugherty

Hi Paula,

There is no easy, infallible way to do what you need done. There is
simply too much variability and unpredictability within such lists.
If you have only a couple hundred names or less to convert I'd just
dig in and do it manually.

If your list is into the thousands of names you may want to read up on
the string manipulation functions and Update Queries.

HTH
 
B

Brainlock

You can always export the table and save it
Jeff (707592) MBSA Beta 2.0
BETA Testing begins 19 May 2005 for MBSA

Nasty.....

it going to be difficult (or impossible) to do automatically, because how do
you work out if the bits of the field are part of the first name, middle,
surname, how many records have you got?

to do a proper job I use the following

title e.g. mr,mrs.miss/ms/dr
first name
middle
surname
salutation e.g. how you would like to refer to them in say a letter, some
would be

Dear Alex

or

Dear Mr White
 
V

vayyao

Rick B said:
Wat about the following:

John D Smith
John Smith
Billy Bob Johnson
Juan San Pedro
Juan Marco San Pedro

You really can't do this easily. Fix your data structure and go back and
fix the records manually. You might try to do SOME of them using update
queries. You could run a query to find all records with only one space.
Then move them into first and last name. You could find all cases where the
middle name is only one character (initial) and sort them into the three
fields. Then, handle whats left manually.

You'll have to play around with the queries to identify the ones you can
using the logic I just mentioned. Or, you might want to just update them
all, then go back and look for exceptions by manually reading the list.

Good Luck.
Click to expand...
 
Top