MS Access using variables to unpivot 100+ columns

Question

I have a pivot table that looks like this (the table layout cannot be changed) with 200 columns to be unpivoted (the fieldnames are indeed numbers)

Template | Row | 1 | 2 | 3 | 4 | ...| 200
abc      | 1   | 5 | 4 |   |   |    |     
abc      | 2   |   | 45|   |   |    |  
abc      | 3   |   | 35|   |   |    |  

The table should look like this:

Template | row | column | value |
abc      |  1  |  1     |    5  |   
abc      |  1  |  2     |    4  |
abc      |  2  |  1     |       |
abc      |  2  |  2     |   45  |      

with 200 columns to be unpivoted, I can't fathom creating a SQL statement with 200 UNION ALLs, so I figured I will loop an insert into by using a variable.

The difficulty I am having is that I don't know how to use the variable both as a value as well as a fieldname. In Pseudo-SQL my query would look like this:

Insert Into TheDestination (Template, Row, Column, Value) Select Template, 
Row, $x as column, TheTable.$x from TheTable

where X would increase by 1 for each iteration.

This is what I came up with, but I am getting a syntax error. What is the correct statement? Do I need to use a 2nd variable?

Private Sub Unpivot_Click()
Dim x As Integer
Dim columncount As Integer
Dim setRST As DAO.Recordset
Dim sqlstr As String

Set setRST = CurrentDb.OpenRecordset("Select * from TheTable")
columncount = setRST.Fields.Count

While Not setRST.EOF
For x = 1 To columncount
CurrentDb.Execute "Insert Into TheDestination VALUES (Template, Rownumber, 
Columnnumber, Result) Select Template, row, "&x&" as column, "&x&" from 
TheTable"

Next x
Wend
End Sub

Thank you in advance!

Answer 1

Your INSERT INTO statement looks really weird, and it appears like you're not assigning the right values.

Using a querydef and parameters avoids problems with string concatenation, and minimizes some assignments.

Try the following:

Private Sub Unpivot_Click()
    Dim x As Integer
    Dim columncount As Integer
    Dim setRST As DAO.Recordset
    Dim sqlstr As String
    Dim qdf As DAO.QueryDef
    Dim fld As DAO.Field

    Set setRST = CurrentDb.OpenRecordset("Select * from TheTable")
    columncount = setRST.Fields.Count
    Set qdf = db.CreateQueryDef ("", "Insert Into TheDestination ([Template], [Row], 
    [Column], [Result]) VALUES (@Template, @RowNumber, @ColumnNumber, @Result)")
    Do While Not setRST.EOF
       qdf.Parameters("@Template") = setRST!Template
       qdf.Parameters("@RowNumber") = setRST!row
       For Each fld In setRST.Fields
           If IsNumeric(fld.Name) Then
                qdf.Parameters("@ColumnNumber") = fld.Name
                qdf.Parameters("@Result") = fld.Value
                qdf.Execute 
           End If
       Next fld
       setRST.MoveNext
    Loop

End Sub