Ms-Access 2007-2010 SQL - from single row record how to create multiple rows 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.
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