Ms-Access 2007-2010 SQL - from single row record how to create multiple rows

Question

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:

table1:
--------------------------------------------------
|  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.

Answer 1

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
            .MoveFirst
            Do
                ' 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.AddNew
                        rstDest!col1 = rstSource!col1
                        rstDest!col2 = rstSource!col2
                        ' set col3 to NULL (empty)
                        rstDest!col3 = Null
                        ' save the new record
                        rstDest.Update
                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.AddNew
                        rstDest!col1 = rstSource!col1
                        rstDest!col2 = rstSource!col2
                        ' col3 is set to the current item in the array
                        rstDest!col3 = Items(a)
                        rstDest.Update
                    Next a
                End If
                ' load the next record from the source table
                .MoveNext
            ' repeat until the end of the source table is reached
            Loop Until .EOF
        End If
        ' close the source table
        .Close
    End With
    ' close the destination table
    rstDest.Close

End Sub

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

SplitIt 

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

Answer 2

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)
    Else
        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) <> ""
union
SELECT table1.col1, table1.col2, Splitter(col3,1) AS Col3
FROM table1 where gimmetext(col3,1) <> ""
union
SELECT table1.col1, table1.col2, Splitter(col3,2) AS Col3
FROM table1 where gimmetext(col3,2) <> ""