Search code examples
sqlvbams-accessunpivot

Unpivot Access Data Table

Good afternoon,

I would like to unpivot an Access Table. I have almost one hundred of columns in Access and one column with the ID and I would like to unpivot, considering all the columns except de ID into one column called "type" (for example).

enter image description here

How could I get it from Access? It doesnt matter if is by a SQL query or VBA.

Thank you in advanced.

Solution

  • It seems that the approaches with SELECT ... do not work for the OP one could try to do it with "stupid" code. Let's assume you have the tables tblSource and tblTarget where tblTarget has the fields ID, type and val and ID is the first field in tblSource. Then the following code will "unpivot" the data

    Option Compare Database
Option Explicit

Sub stupidUnpivot()

    Dim db As Database
    Set db = CurrentDb
    Dim rsSrc As Recordset
    Set rsSrc = db.OpenRecordset("SELECT * FROM tblSource")
    
    Dim rsTrg As Recordset
    Set rsTrg = db.OpenRecordset("SELECT * FROM tblTarget")
    
    Dim i As Long
    
    rsSrc.MoveFirst
    
    Do
        With rsTrg
            ' every row in tblSoruce regardless of the number of columns
            ' will be written to tblTarget in the fields ID, type and val
            For i = 1 To rsSrc.Fields.Count - 1
                .AddNew
                .Fields("ID").Value = rsSrc.Fields(0).Value
                .Fields("type").Value = rsSrc.Fields(i).Name
                .Fields("val").Value = rsSrc.Fields(i).Value
                .Update
            Next i
        
        End With
        rsSrc.MoveNext
    Loop Until rsSrc.EOF

End Sub