Ruminati

Unpivot

Crosstabs, also known as pivots, are supported directly by SQL-92 and later. They provide a compact "spreadsheet-style" summary representation of data that varies in two (or more) dimensions.

However, it is extremely difficult to use relational manipulation techniques on data that has already been crosstabulated. Also, users seem to think that any rectangular grid of values constitutes a table and are just as likely to give you cross tabulated data.

Most dialects of SQL provide no support whatsoever for "unpivoting" such a structure. Microsoft Transact-SQL provides limited support but no general solution for an indeterminate number of columns.

So, here's some code: the code you kept promising yourself you'd write and never got around to.

Sub Unpivot(tablename As String, startCol As String)
    Dim t1 As DAO.Recordset
    Dim createT2 As String
    Dim f As Field
    Dim skip As Boolean
    Dim i As Integer
    Dim nonPivotedCols As String
    
    skip = False
    Set t1 = CurrentDb.OpenRecordset(tablename)
    Dim startColOrdinalPos As Integer
    startColOrdinalPos = t1.Fields(startCol).OrdinalPosition
    For i = 0 To startColOrdinalPos - 1
        nonPivotedCols = nonPivotedCols & t1.Fields(i).Name & ","
    Next
    
    createT2 = "SELECT " & nonPivotedCols & "'" & t1.Fields(startColOrdinalPos).Name & "' AS UnpivotedColumn, " & t1.Fields(startColOrdinalPos).Name & " AS UnpivotedValue INTO [" & tablename & " unpivoted] FROM " & tablename
    Debug.Print createT2
    CurrentDb.Execute createT2
    
    For i = startColOrdinalPos + 1 To t1.Fields.Count - 1
        createT2 = "INSERT INTO [" & tablename & " unpivoted] (" & nonPivotedCols & "UnpivotedColumn,UnpivotedValue) SELECT " & nonPivotedCols & "'" & t1.Fields(i).Name & "'," & t1.Fields(i).Name & " FROM " & tablename
        Debug.Print createT2
        CurrentDb.Execute createT2
    Next
End Sub

The underpinning notion is that after a certain point all the columns are pivot generated.