Separation

[mshakhz via AccessMonster.com]

Hello everyone,

I have an issue - i need to separate last name and first name from a column
that has both.
There are no special characters, every entry looks something like this "Mary
Johnes".

I guess one way I could do it is manually, but benig impatient and lazy I
want to find a faster way.

My questions are:
1) Is there a known solution to this problem?

2) Say I create FirstName and LastName columns in the same table and then
insert in each of those columns all of the values from the Name column. Is
there a way I could trim each using the wildcards (something like [ ]% and %[
])?
Or, could I use wildcards in the append query and insert only the last or
first names?

Sorry if I'm not very clear in my last question


thanks!

~Martik

 

[KARL DEWEY]

Yes, create FirstName and LastName columns in the same table. Maybe also
have fields for middle name/inital and sufix -- Jr, Sr, II, IV, etc.

Then use up date query extracting the parts.

BUT you will need lots of manual because of the following examples --
Billy Joe Smith
Billy J. Smith
Billy J Smith Sr
Billy Smith Jr
Mary Jo De La Costa

Read about use of Left, Mid, Right, InStr, and InStrRev.

 

[mshakhz via AccessMonster.com]

Thank you Karl!
Got it:
SELECT left(Name, instr(name, " ")) AS FirstName

etc.

Yes, create FirstName and LastName columns in the same table. Maybe also
have fields for middle name/inital and sufix -- Jr, Sr, II, IV, etc.

Then use up date query extracting the parts.

BUT you will need lots of manual because of the following examples --
Billy Joe Smith
Billy J. Smith
Billy J Smith Sr
Billy Smith Jr
Mary Jo De La Costa

Read about use of Left, Mid, Right, InStr, and InStrRev.

Hello everyone,

[quoted text clipped - 21 lines]

~Martik

 

[mshakhz via AccessMonster.com]

SELECT right(name, len(name) - instr(name, " ") AS LastName

 

[KARL DEWEY]

SELECT left(Name, instr(name, " ")-1) AS FirstName

Minus one so as not to include the space.

--
Build a little, test a little.

Thank you Karl!
Got it:
SELECT left(Name, instr(name, " ")) AS FirstName

etc.

Yes, create FirstName and LastName columns in the same table. Maybe also
have fields for middle name/inital and sufix -- Jr, Sr, II, IV, etc.

Then use up date query extracting the parts.

BUT you will need lots of manual because of the following examples --
Billy Joe Smith
Billy J. Smith
Billy J Smith Sr
Billy Smith Jr
Mary Jo De La Costa

Read about use of Left, Mid, Right, InStr, and InStrRev.

Hello everyone,

[quoted text clipped - 21 lines]

~Martik

 

[John W. Vinson]

SELECT right(name, len(name) - instr(name, " ") AS LastName

or slightly simpler:

SELECT Mid([name], InStr([name], " ") + 1) AS LastName

The same expressions can be used in an Update query to populate the firstname
and lastname fields. If you do so, run a subsequent query with a criterion of

LIKE "* *"

on LastName to find the "Mary Jo" and "De La Beckwith" anomalies.

 

[mshakhz via AccessMonster.com]

Good Point! Thank you very much!

SELECT left(Name, instr(name, " ")-1) AS FirstName

Minus one so as not to include the space.

Thank you Karl!
Got it:

[quoted text clipped - 21 lines]

 

[mshakhz via AccessMonster.com]

Of course that makes much more sense!
Thank you very much!

Martik

SELECT right(name, len(name) - instr(name, " ") AS LastName

or slightly simpler:

SELECT Mid([name], InStr([name], " ") + 1) AS LastName

The same expressions can be used in an Update query to populate the firstname
and lastname fields. If you do so, run a subsequent query with a criterion of

LIKE "* *"

on LastName to find the "Mary Jo" and "De La Beckwith" anomalies.