Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2014
    Posts
    1

    Unanswered: Create rows of data from columns

    So I have a large dataset with very minimal columns.
    Something like this:
    Primary_Key ITEM1 ITEM2 ITEM3 STATUS
    123456ABC FOOD DRINK SILVERWARE PAID




    I would like to have that turn into:
    Primary_Key ITEMS STATUS
    123456ABC FOOD PAID
    123456ABC DRINK PAID
    123456ABC SILVERWARE PAID

    I have almost 8,000,000 rows of data, so I know this is going to triple my records but wanted to know if it can be done in VB in Access?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    Sub SplitRows()
    
        Const c_PK As Long = 0          ' Ordinal position of the Primary Key column.
        Const c_ItemFirst As Long = 1   ' Ordinal position of the column containing the first item.
        Const c_ItemLast As Long = 3    ' Ordinal position of the column containing the last item.
        Const c_Status As Long = 4      ' Ordinal position of the Status column.
        Const c_SQL As String = "INSERT INTO TableDestination  (Primary_Key, Items, Status) VALUES ('@K', '@I', '@S');"
        
        Dim rst As DAO.Recordset
        Dim i As Long
        Dim strSQL As String
        Set rst = CurrentDb.OpenRecordset("TableSource", dbOpenSnapshot)
        With rst
            Do Until .EOF
                For i = c_ItemFirst To c_ItemLast
                    strSQL = Replace(Replace(Replace(c_SQL, "@K", .Fields(c_PK).Value), _
                                                            "@I", .Fields(i).Value), _
                                                            "@S", .Fields(c_Status).Value)
                    CurrentDb.Execute strSQL, dbFailOnError
                Next i
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    Be aware that in the destination table the name Primary_Key for the first column makes no sense as its contents will obviously be duplicated.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •