I need help in below table updation using SQL in Ms-Access 2007-2010 database.

I've a table called table1 which has below entries:

|  col1  |   col2   |          col3         |
|    A1  |  abcd    |      123;456;789      |
|    B2  |  efgh    |       246;369         |
|    C3  |   ijk    |                       |

Using SQL, I want to change the above table (particularly the column col3 multiple values into individual multiple rows) as shown below:

|  col1  |   col2   |          col3       |
|    A1  |  abcd    |         123         |
|    A1  |  abcd    |         456         |
|    A1  |  abcd    |         789         |
|    B2  |  efgh    |         246         |
|    B2  |  efgh    |         369         |
|    C3  |   ijk    |                     |

How do I do it? I tried various forums. but could not come up with exact solution so far.

Appreciate your help.

Thanks, Jak.

  • 3
    You will need VBA, so I think Stackoverflow would be more appropriate.
    – Remou
    Mar 15 '13 at 16:38

Easy answer is to do this in a function in the built-in VBA editor in Microsoft Access.

Assuming you have two tables, Source and Dest that both have 3 columns, col1, col2, and col3.

Sub SplitIt()

    Dim rstSource As Recordset
    Dim rstDest As Recordset
    Dim Items() As String

    ' use dbOpenSnapshot to open the source table READ-ONLY
    Set rstSource = CurrentDb.OpenRecordset( _
            "SELECT col1, col2, col3 FROM Source;" _
            , dbOpenSnapshot)
    ' use dbOpenDynaset to open the destination table READ-WRITE
    Set rstDest = CurrentDb.OpenRecordset( _
            "SELECT col1, col2, col3 FROM Dest;" _
            , dbOpenDynaset)

    With rstSource
        ' .BOF is Beginning of the table
        ' .EOF is End of the table
        ' Checking if both are false means there are records in the
        ' source table
        If Not (.BOF And .EOF) Then
            ' get the first record from the source table
                ' if col3 is NULL (empty)
                If Nz(!col3, "") = "" Then
                        ' add a new record into the destination table
                        ' with data from the source table for col1 and col2
                        rstDest!col1 = rstSource!col1
                        rstDest!col2 = rstSource!col2
                        ' set col3 to NULL (empty)
                        rstDest!col3 = Null
                        ' save the new record
                Else ' if col3 IS NOT NULL
                    ' convert col3 into an array of strings
                    Items = split(Nz(!col3, ""), ";")
                    ' loop through the array of strings
                    For a = LBound(Items) To UBound(Items)
                        rstDest!col1 = rstSource!col1
                        rstDest!col2 = rstSource!col2
                        ' col3 is set to the current item in the array
                        rstDest!col3 = Items(a)
                    Next a
                End If
                ' load the next record from the source table
            ' repeat until the end of the source table is reached
            Loop Until .EOF
        End If
        ' close the source table
    End With
    ' close the destination table

End Sub

Then run the SplitIt() function from the Immediate Window. Press CTRL-G then type:


and press [Enter]

As an aside; there is an excellent resource on understanding For...Next code at http://msdn.microsoft.com/en-us/library/5z06z1kb.aspx

  • Max,Thanks for your reply. But when I executed your code above, I got below error message: "Run-time error '3027': Cannot update. Database or object is read-only" at "rstDest.AddNew" line. Pls help me little more. Thanks, Jak.
    – Jak
    Mar 18 '13 at 19:41
  • I did test my code and verified that it works. The error you are getting indicates you may have the dest table open in design view. Try closing the database, re-opening it, and running the code again.
    – Hannah Vernon
    Mar 19 '13 at 0:54
  • Max, you were right. I did have the database open and hence the system was giving the error message. other question I've is, you assumed that the Dest table is already created, sorry to ask you as I am a newbie in the programming world, but the Dest table will be created from Source table, is it not? or your are jus referring to the table structure here? If it's just a table structure, then how do we create a table structure in Ms-Access 2007-2010 db? Appreciate your help my friend. Thanks, Jak.
    – Jak
    Mar 19 '13 at 17:15
  • Dest is merely a copy of source. You would need to modify my code to use the names of your actual tables. My code is just an example of how to do this.
    – Hannah Vernon
    Mar 19 '13 at 17:22
  • 2
    I am sorry, I am not able to upvote the solution but I've added it as Answered. Thanks again for your help Max.
    – Jak
    Mar 20 '13 at 15:58

MSAccess doesn't seem to have the ability to return a really nice table from a UDF (like TSQL does). The best I could come up with was to create a MS Access function to split and return a chunk and union several results. This is pretty hackish, but should get the job done using a query instead of a VBA batch/macro.

MS Access function (VBA):

Public Function Splitter(col As String, pos As Integer) As String
    Dim arr() As String
    arr = Split(col, ";")
    If UBound(arr) >= pos Then
        Splitter = arr(pos)
        Splitter = ""
    End If
End Function

MS Access query to demonstrate it:

SELECT table1.col1, table1.col2, Splitter(col3,0) AS Col3
FROM table1 where gimmetext(col3,0) <> ""
SELECT table1.col1, table1.col2, Splitter(col3,1) AS Col3
FROM table1 where gimmetext(col3,1) <> ""
SELECT table1.col1, table1.col2, Splitter(col3,2) AS Col3
FROM table1 where gimmetext(col3,2) <> ""
  • You may want to change gimmetext into Splitter in your query sample...
    – Hannah Vernon
    Mar 15 '13 at 20:37
  • I also had to turn col3 into Nz([col3],"") to get this to work. Having said that, it misses the row with the null col3 value. But that is assuming the empty value in col3 is NULL and not "" (zero-length string)
    – Hannah Vernon
    Mar 15 '13 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.