Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
3  
You will need VBA, so I think Stackoverflow would be more appropriate. –  Remou Mar 15 '13 at 16:38
add comment

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
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. –  Max 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. –  Max 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
show 6 more comments

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) <> ""
share|improve this answer
    
You may want to change gimmetext into Splitter in your query sample... –  Max Vernon Mar 15 '13 at 20:37
    
@Max Vernon: good catch. Thanks. –  TimG Mar 15 '13 at 20:41
    
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) –  Max Vernon Mar 15 '13 at 20:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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