i have that very same form...user picks item to pivot:
The user picks a Set from the combo box cboSet, then it builds the query
qsXtab0 is a base query with fields in it.
Code:
Private Sub MakeXtabSql(ByVal pvQry)
Dim sSql As String
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs(pvQry)
sSql = "TRANSFORM Sum(qsXtab0." & cboSet & ") AS SumOfFld " & _
"SELECT qsXtab0.ProdLine, '" & cboSet & "' AS DataSet, Sum(qsXtab0." & cboSet & ") AS [TotalOf " & cboSet & "] " & _
"FROM qsXtab0 " & _
"GROUP BY qsXtab0.ProdLine, '" & cboSet & "'" & _
"PIVOT Format([ProdDate],'yyyy-mm');"
qdf.SQL = sSql
qdf.Close
docmd.openquery pvQry
Set qdf = Nothing
End Sub