I have a table that looks like:

Question

I have a table that has

     Material   90    89     88    87    ….2     1     0
      123456    34    30     26    38     14    12     8
      123457    47    42     33    54     38    27    42

And I want to convert it to

     Date        123456     123457              
    Date()-90       34         47
    Date()-89       30         42
    Date()-88       26         33
    Date()-87       38         54
       ….        …………        ………..
    Date()-2        14         38
    Date()-1        12         27
    Date()           8         42

I found this, but am trying to figure out how to use it:

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

So far, I have:

Public Function Unpivot()

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("SSMaterialByDayQ322")
columncount = setRST.Fields.Count
Set qdf = db.CreateQueryDef("", "Insert INTO SSHistoryQ322 ([Date], [Row], [Column], [Result]) VALUES (@Template, @RowNumber, @ColumnNumber, @Result)")
Do While Not setRST.EOF
   qdf.Parameters("@Template") = setRST!Date
   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 Function

So--What is the question? What message on what line are you getting, or what do the results look like? What are the values when that message appears? — donPablo, Aug 01 '18 at 03:02
Possible duplicate of [I want to transpose rows into columns in ms-access](https://stackoverflow.com/questions/40593728/i-want-to-transpose-rows-into-columns-in-ms-access) — Andre, Aug 01 '18 at 07:35
I doubt creating/modifying query with QueryDefs will achieve this. You need to save data to a table. How many values of Material are possible? Table has a limit of 255 fields. — June7, Aug 01 '18 at 07:41
on the Set qdf = db.createquerydef("", "insert into.... I get Object Required. — user3470177, Aug 01 '18 at 16:26
I have about 1800 material plant combinations, but can manage by plant to limit the number of materials in each transposed table to less than 254 — user3470177, Aug 01 '18 at 16:27
@user3470177 you might try dim db as database and set db = currentdb in order to create the Object Required — donPablo, Aug 04 '18 at 01:09

June7 · Answer 1 · 2018-08-01T23:04:51.610

If there are not more than 50 fields in the table needing to be 'unpivoted', a UNION query can rearrange the data to a normalized structure. There is no query builder or wizard for UNION, must type or copy/paste in SQL view. UNION has limit of 50 SELECT lines.

SELECT "Date()-90" AS [Date], Material, [90] AS Data FROM SSMaterialByDayQ322
UNION SELECT "Date()-89", Material, [89] FROM SSMaterialByDayQ322
UNION SELECT "Date()-88", Material, [88] FROM SSMaterialByDayQ322
UNION SELECT "Date()-87", Material, [87] FROM SSMaterialByDayQ322
UNION SELECT "Date()-2", Material, [2] FROM SSMaterialByDayQ322
UNION SELECT "Date()-1", Material, [1] FROM SSMaterialByDayQ322
UNION SELECT "Date()-0", Material, [0] FROM SSMaterialByDayQ322;

If there are more than 50 year fields, VBA can write records to table, consider:

Public Sub Unpivot()
Dim x As Integer
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SSMaterialByDayQ322")
Do While Not rst.EOF
   For x = 1 To rst.Fields.Count - 1
        CurrentDb.Execute "INSERT INTO SSHistoryQ322([Date], Material, Data)" & _
                          "VALUES('Date()-" & rst.Fields(x).Name & "', " & rst!Material & ", " & rst.Fields(x) & ")"
   Next
   rst.MoveNext
Loop
End Sub

Then as long as there aren't more than 254 unique Material values, build CROSSTAB from the query or table.

TRANSFORM First(Query1.Data) AS FirstOfData
SELECT Query1.Date
FROM Query1
GROUP BY Query1.Date
PIVOT Query1.Material;

Recommend not using Date as a field name because it is a reserved word.

the date()-90, date()-89....date()-2, date()-1, date() implies that there are 91 columns values for each row. Last I tried a union, the limit was indeed 50, plus it's very slow. — user3470177, Aug 01 '18 at 16:30

score 0 · Answer 2 · answered Aug 01 '18 at 12:33

You can start by making a union query

SELECT 'Date()-90' As [Date], Material, [90] As [Value] FROM TheTable
UNION ALL
SELECT 'Date()-89' As [Date], Material, [89] As [Value] FROM TheTable
UNION ALL
SELECT 'Date()-88' As [Date], Material, [88] As [Value] FROM TheTable
UNION ALL
SELECT 'Date()-87' As [Date], Material, [87] As [Value] FROM TheTable
...
UNION ALL
SELECT 'Date()-02' As [Date], Material, [2] As [Value] FROM TheTable
UNION ALL
SELECT 'Date()-01' As [Date], Material, [1] As [Value] FROM TheTable
UNION ALL
SELECT 'Date()-00' As [Date], Material, [0] As [Value] FROM TheTable

Of course, you can use VBA to create this query in a loop. Store this SQL-Text as query qryFlatten.

Now we build a pivot query from this query. This yields our final result

TRANSFORM Sum(qryFlatten.Value) AS SumOfValue
SELECT qryFlatten.Date
FROM qryFlatten
GROUP BY qryFlatten.Date
ORDER BY qryFlatten.Date DESC 
PIVOT qryFlatten.Material;

I have a table that looks like:

2 Answers2