Splitting Lastname, First Name Middle Initial Suffix 1

[nemmeh]

Hey everyone, got a little query I need some help with. I'm sure it's not too complicated, but I'm quite a beginner when it comes to constructing sql queries.

I need the query to be in sql plz, because I have no experience with VBA.

Basically I have a field "Name" I need to split this up into two seperate fields. "Lastname" and "Firstname".

Below is an example of the data in the "name" field currently.

Woods, Tiger G. Jr.

I need a query that will select from the left until it hits a , then I need it to insert that data into the "lastname" field. Then I need a query that will delete that bit of selected information from the "name" field. Then I'll just rename the "name" field to "firstname".

I'm not worried about the , getting put into the lastname field if it's a problem to get rid of it. I can just do an export into excel and delete the , off that way.

Like I said, I'm a beginner so I'm sure there is a much simplier and more efficient way of doing it.

Any help you can give me is very much appreciated. Thank you.

 

[GoDawgs]

Not too tough...just set up a new query, pull in the table containing the Name field...then create columns in the query with this:

Lastname: Left([Name],InStr([Name],",&quot-1)

TheRest: Right([Name],Len([Name])-InStr([Name,",&quot-1)

That will work as long as the comma is there and there's only one comma in every record. You can make this query an update query or make-table query...not sure what you wanted the next step to be. Hope that helps.

Kevin

 

[GoDawgs]

Oops...forgot to close the bracket after the last Name field...TheRest should look like this:

TheRest: Right([Name],Len([Name])-InStr([Name],",&quot-1)

 

[nemmeh]

awesome, working perfectly except in my case I didn't need the -1 on TheRest statement.

Thank you so much.

 

[ordendelfai]

This almost solves my problem I have, but not quite. I have names that are formatted in a single field [Name]like:

LUCAS GEORGE K MD

I need 4 new columns in a query that will pull the "last name" to the left of the first space, the "first name" to the right of the first space, the "middle initial" to the left of the last space, and the "title" to the right of the last space.

I cannot figure out how to do this. I do not mind using VBA if SQL cannot do it.

Thanks ,

Joel

 

[jdegeorge]

Kevin,

Yes, not too tough, but you figured it out and it solved my problem. That deserves a star from me!

Jim DeGeorge