MS Access - parse/split strings from a column

[Loco3KGT]

I just took a whole lot of Excel spreadsheets and imported them into MS Access.. now it's an employee list and the person who created the spreadsheets only used one cell for the persons name.. so now in Access instead of having two columns : First Name + Last Name , I just have Name with John Doe..

I'd like to know the best way to take "Name" and split it and put it into the columns First Name + Last Name for the same row.

On my personal machine I'd just use PHP to tokenize the string and throw it back into the database (mysql).. but I don't know if that's even possible with Access....

Linux/OSS geek in MS world = pain.

 

[bunker]

Save the spreadsheet as a .txt file
Import it into Access using a space as the delimiter.

 

[Loco3KGT]

that means redoing it all :-( there's got to be another way...

 

[Loco3KGT]

actually it won't work because there are other things with spaces... and it'll completely shaft me on those.

 

[bunker]

Ahhh...thought it was just a list of names.

Let me think on this one a bit. Some sort of update query is probably needed.

 

[Loco3KGT]

gracias.

i'm really beginning to ponder calling up the CIO and seeing if I'm allowed to just do a PHP/MYSQL setup for this. But that'd take the rest of the day to get running and accomplish this.

 

[DaveBrown]

You can do this within access using an update query. First you need to create two new text fields called "Firstname" and "Lastname". I'll assume that your original name field is called "Name" and the the table is "table1". Then run this update query:

UPDATE table1 (Firstname, Lastname) VALUES ( Left([name],InStr([name]," ")-1), Mid([name],InStr([name]," ")+1) )


The firstname field will be populated with everything up to the first space. And the lastname field will be populated with everything after the first space.

 

[Descartes]

VBA, the scripting engine that Access uses for non-SQL functions, has many string functions. What you need are Mid, InStr, Left, Right, etc. Using your example, you could do this:

select Left(nameField, InStr(1, nameField, " ") - 1) as firstName, Mid(nameField, Instr(1, nameField, " ") + 1) As lastName
from yourTable

VBA also has Split() which is analogous to tokenize(), but it's of little use in the context of a SQL statement.

There are very few things I like about Access, and the coupling of the Jet engine to VBA is one of them.

 

[Descartes]

I guess I got in there too late

 

[Loco3KGT]

thanks for the responses, I'm going ot try the update query. Part of the handicap here is I know jack about Access, and when it comes to databasing, I'd like a shell (a la mysql). So I'll go figure out how I'm supposed to do the stuff DaveBrown just offered.

 

[Loco3KGT]

ok I'm gonna need to be told how to feed access my own sql code :-|

 

[DaveBrown]

Yes, I can see how this is not obvious to someone new to Access

To run the update query:

1. select Queries from the menu on the left.
2. click New.
3. leave the selection on design view, and click OK
4. Hit the close button on the Show table dialog.
5. Click the SQL button in the top left toolbar (or Use the view Menu, and pick SQL)
6. Paste in the update query
7. Click the exclamation point (run) button (which is on either the toolbar or under the Query menu).
8. Click OK if you are asked to confirm.

That does it. After the query has run, there is no need to save it. But if you want you can save it as Query1 (default) or whatever name you like for later reference.

Dave

 

[Loco3KGT]

syntax error :-| (and yea, I changed the name of table1 to my table's name ;-) )

And what's this going to do to people named

John Doe Jr ?

 

[Loco3KGT]

I got Descartes' working for a select.. now I just need to feed that back into the database

 

[DaveBrown]

Darn! You are right - my SQL code was in error. This will teach me to test out code before posting The correct code is as follows:

UPDATE table1 SET Firstname=Left([name],InStr([name]," ")-1), LastName=Mid([name],InStr([name]," ")+1)

I tested it this time, so it should work.

To answer your question, if the Name is JOhn Doe Jr. then the firstname will be "John" and the lastname will be "Doe Jr." I know that probably isn't what you would like. I think in cases like this your best bet is to just enter the first and last name yourself. You could make the task slightly easier by searchig for "Jr" or a "." in the name.

-Dave

 

[Loco3KGT]

Thanks a lot for your help. I ended up finding the errors and getting it working. Spent the rest of my time getting the reports in order for the higher ups.

Thanks so much for your help fellas.