How do I sort one column by the second word in that column?

[Guest]

I have a person's name in one column eg: Ginny Roby. I need to either sort
by Roby or divide the column into two separate names 1: Ginny 2: Roby. Can
this be done?

 

[John Vinson]

I have a person's name in one column eg: Ginny Roby. I need to either sort
by Roby or divide the column into two separate names 1: Ginny 2: Roby. Can
this be done?

I'd suggest dividing. Create two new fields, FirstName and LastName.
Run an Update query updating FirstName to

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

and LastName to

Mid([name], InStr([name], " ") + 1)

Then you'll need to run a Query using a criterion of

LIKE "* *"

on LastName; you very likely have people with names like "Billy Bob
Thornton" who will end up with "Bob Thornton" in the LastName (his
first name is Billy Bob, just ask him). Names like "Ludwig von
Beethoven" will be handled correctly though.

John W. Vinson[MVP]

 

[Jack Marks]

It would be much easier to learn how to do this using Excel. Put a few
funny names in the first column, and use the command left, right, and
concatenate. Each of those things has nice help screens which would tell
you how to do it. It would be fast, and then, when you know what you are
doing,do the same thing in Access.

I have a person's name in one column eg: Ginny Roby. I need to either
sort
by Roby or divide the column into two separate names 1: Ginny 2: Roby.
Can
this be done?

I'd suggest dividing. Create two new fields, FirstName and LastName.
Run an Update query updating FirstName to

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

and LastName to

Mid([name], InStr([name], " ") + 1)

Then you'll need to run a Query using a criterion of

LIKE "* *"

on LastName; you very likely have people with names like "Billy Bob
Thornton" who will end up with "Bob Thornton" in the LastName (his
first name is Billy Bob, just ask him). Names like "Ludwig von
Beethoven" will be handled correctly though.

John W. Vinson[MVP]