How do I batch cut and paste across columns in Access 2007

I have a Access Database that has a name field with both last, first and middle names (ex: Smith, John H.) in the same field. I want to separate them into individual columns and fields. There are 12,000 names.
How can I do this?
LVL 3
mitzillaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
place this function in a regular module

then write a query like this

select [name], splitname(1,[name]) as firstName,splitname(2,[name]) as LastName,splitname(1,[name]) as MI
from tableX

Function SplitName(ByVal idx As Integer, ByVal sName As String) As String

    Dim infArr() As String
   
    SplitName = ""
    If sName <> "" Then
        infArr = Split(sName, " ")
        Select Case idx
            Case 1
                SplitName = infArr(0)
            Case 2
                If UBound(infArr) > 0 Then SplitName = infArr(1)
            Case 3
                If UBound(infArr) > 1 Then SplitName = infArr(2)
            Case 4
                If UBound(infArr) > 2 Then SplitName = infArr(3)
            Case 5
                If UBound(infArr) > 2 Then SplitName = infArr(4)

        End Select
    End If
End Function

Open in new window

0
Dangle79Commented:
I don't know that there's an easy way just within Access to do it, but it could be done with a vb-script. if you've already got a connection to the database w/ your ASP page and the added columns exist...

pull recordset
  FOR each record
    set recordID  < key variable from record
    set namevariable  < from original field
    separateName = split(namevariable, " ")
    sql update recordID  lastname = separateName(0) firstname = separateName(1) initial = separateName(2)

could do some additional fiddling with the text to peel off the commas and periods as well. save that asp page (i'm assuming from the zones listed you're working within a web application) and run it once to see what you get. obviously run against a backup copy of your database first.
0
c661jmbCommented:
You could export the table into excel and then open the excel file, then in three columns create formulas.

I have attached a sample spreadsheet with the formulas.

This will work so long as the full name column has the data in the format:

Surname, Forname Initial


sample.xls
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Patrick MatthewsCommented:
mitzilla,If the data are all formatted correctly, then this can usually be handled with formulas.  That said, deviations in formatting can really, really trip you up: some records may have titles and/or suffixes and some not, for example.I go over a number of the challenges, and offer some very robust VBA code, in dealing with this in my article here:http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1819-Parsing-Names-in-MS-Office-Visual-Basic-6-and-Visual-Basic-for-Applications.htmlPatrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
That article includes a sample MDB file.
0
Patrick MatthewsCommented:
Also keep in mind that multi-word name parts are always, always going to give you fits :)
0
mitzillaAuthor Commented:
Thanks everyone!
0
Rey Obrero (Capricorn1)Commented:
mitzilla,

you did not even try the first post ???
0
mitzillaAuthor Commented:
Capricorn1,
Actually I  tried all of the posts. But my understanding of access is still really really at a beginner status.
I couldn't get the module to be seen by the query.  I had to poke around for like fifteen minutes before I even found that feature. Apologies, I'm sure yours would of worked perfectly and I did give it a shot. I just couldn't get my head around it.
Thanks
0
Rey Obrero (Capricorn1)Commented:
<I couldn't get the module to be seen by the query.  >

you could have posted a question, so the other will know if you are having difficulty with the posted suggestion and can post information to help you with the problem.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.