Sort Names by Alphabetical order

[oaklandar]

I have a table with an id field and name field:

ID    Name
1     Mark Rivers
2     Jack Jones
3     Rich Thompson
4     Steve Accorn

I want to make a query where I get this result by Alphabetical order on Last Name:

Steve Accorn
Jack Jones
Mark Rivers
Rick Thompson

Is there anyway I can do this in SQL. I think I need to split the name field between the first name and last name using space as a delimeter and then sorting the last name.
Please advise how I can do this?

 

[PHV]

Something like this ?
SELECT Trim(Mid([name], 1 + InStr([name], " "))) As LastName, [id], [name]
FROM yourTableName
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[florida41]

Thanks but it didnt seem to work. I am using Access 2000 datbase.

 

[lespaul]

Did you get some kind of error message?

What about:

SELECT Trim(Mid([name], 1 + InStr([name], " "))) As LastName, [id], [name]
FROM yourTableName
ORDER BY Trim(Mid([name], 1 + InStr([name], " ")));



Leslie