Writing expression which selects last names from field of full nam

[Guest]

I am converting a large database to Access and need to divide a field
containing full names into seperate fields (ie., First name, middle initial
and last name).

 

[Tom Ellison]

Dear Lily:

First look at your data. Devise a "rule" that will accomplish what
you want. Code the rule and test it. Find exceptions to the rule.
Improve the rule and try again.

When you have something specific, such as how to implement the rule,
come back and describe it if you need help.

The reason I say this is the what will work for you might not work for
other situations. This tends to be quite custom. For example, there
would be different answers to these questions for different data you
might encounter in various databases:

Is there always a middle initial? Is there ever a title (Mr., Dr.,
Prof., etc.). Is the middle intial ever/always followed by a period?
Do some of the people have a two-word last name?

Only a careful look at your data will begin to suggest what rules you
would need to do a decent job of this. There's no one rule that works
worldwide. Like I said, it's custom.

A problem with custom approaches to something like this is that what
works well today may not work so well tomorrow. This is especially
true if new people begin entering data, or if the nature of the data
changes, such as introducing a long list of Chinese names for the
first time, or names in the Cryllic alphabet.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Dear Tom,

This is a one-time event. The rule is: "pick the last (or first or middle)
word from a field which is normally three words long and use it as the value
for a new field". The problem is that I can't seem to come up with the code
to do this correctly. I don't need anything absolutely perfect - on a one
time basis I can clean up the instances where I get suffixes rather than last
names, etc., much more easily than just copy-pasting thousands of last names.

Daylily

 

[John Spencer (MVP)]

Assuming that your FullName field always has three names in it and the order is
First Middle Last and this is a one shot deal. I would probably be lazy and use
a series of queries to do this.

FIRST-make a backup of your data. It is always nice to be able to get back to
the starting point, when something goes awry. I would also add a temporary full
name field and populate it with all the data in the fullName field.

BackUp Copy:
UPDATE [YourTableName]
SET [BackupFullNameField] = Trim([FullName])
WHERE [FullName] Is Not Null

FirstName:
UPDATE [YourTableName]
SET [FirstNameField] =
Trim(Left([BackupFullNameField],Instr(1,]BackupFullNameField]," ")))
WHERE [BackupFullNameField] Like "* *"

Strip Off the first name:
UPDATE [YourTableName]
SET [BackupFullNameField] =
Trim(Mid([BackupFullNameField],Instr(1,]BackupFullNameField]," ")))
WHERE [BackupFullNameField] Like "* *"

MiddleName - only if there is a space in the name:
UPDATE [YourTableName]
SET [MiddleNameField] =
Trim(Left([BackupFullNameField],Instr(1,]BackupFullNameField]," ")))
WHERE [BackupFullNameField] Like "* *"

Strip off the middle name
UPDATE [YourTableName]
SET [BackupFullNameField] =
Trim(Mid([BackupFullNameField],Instr(1,]BackupFullNameField]," ")))
WHERE [BackupFullNameField] Like "* *"

LastName:
UPDATE [YourTableName]
SET [LastNameField] = Trim([BackupFullNameField])
WHERE [BackupFullNameField] Is Not Null

That could all be done in one query, but it would be hard to read and hard to
understand. Since you are doing this one time, go the easy way.