• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

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?
0
mitzilla
Asked:
mitzilla
  • 3
  • 3
  • 2
  • +2
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now