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).
How could I get it from Access? It doesnt matter if is by a SQL query or VBA.
Thank you in advanced.
CodePudding user response:
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 colummns
' 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