Transposing columns

[Guinaba]

Hello experts,

I am trying to create a pivot table from an unpivot table (pics) below:


Pivot table:

Unpivot table:

Using the following code:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim tbl As TableDef
Set db = CurrentDb

For Each tbl In db.TableDefs
    If tbl.Name = "T_Pivot" Then
    db.Execute "DROP TABLE T_Pivot"
    Else
    End If
Next tbl

db.Execute "CREATE TABLE T_Pivot(Stores CHAR, Cities CHAR, Period DATETIME, SalesQty INTEGER);"

Set db = CurrentDb

Set rst = db.OpenRecordset("T_Unpivot", dbOpenTable)

Dim n As Integer
Dim fieldname As String
Dim str As String

 Do
        For n = 1 To rst.Fields.Count - 1
            fieldname = rst.Fields(n).Name
            str = "Insert Into T_Pivot (Stores,Cities,Period,SalesQty) " _
            & "VALUES (" & rst![Stores] & ", " & rst![Cities] & ", " & rst![Period] & "," & rst![SalesQty] & ")"
            Debug.Print str
            Next n
            'rst.MoveNext
            db.Execute str
 Loop Until rst.EOF
End Sub

However, not sure I am not able to recognize the fields Period and SalesQty, any suggestion?

 

[EFANYoutube]

rst has you unpivot table data and therefore does not have those 2 fields. You need to reference by index or date name

 

[Guinaba]

rst has you unpivot table data and therefore does not have those 2 fields. You need to reference by index or date name

Thanks @EFANYoutube, could you show me how to reference by index? Do you have an example?

 

[EFANYoutube]

Give me a little time and I will write something up for you

 

[EFANYoutube]

Thanks for you Patience
I went about it a different way
I opened the new table you created and from i created a single record per each dated Columns using the static fields to fill in the other information

Option Compare Database

Sub DoSomething()
Dim db As DAO.Database
Dim UnPivot As DAO.Recordset
Dim Pivot As DAO.Recordset

Dim tbl As TableDef
Set db = CurrentDb

For Each tbl In db.TableDefs
    If tbl.Name = "T_Pivot" Then
        db.Execute "DROP TABLE T_Pivot"
    End If
Next tbl

db.Execute "CREATE TABLE T_Pivot(Stores CHAR, Cities CHAR, Period DATETIME, SalesQty INTEGER);"


Set Pivot = db.OpenRecordset("T_Pivot", dbOpenTable)
Set UnPivot = db.OpenRecordset("T_Unpivot", dbOpenTable)

Dim Fld As Integer
Dim str As String

 Do
    With Pivot
        For Fld = 2 To UnPivot.Fields.Count - 1
            .AddNew
            .Fields("Cities").Value = UnPivot.Fields("Cities").Value
            .Fields("Stores").Value = UnPivot.Fields("Stores").Value
            .Fields("Period").Value = DateValue(UnPivot.Fields(Fld).Name)
            .Fields("SalesQty").Value = UnPivot.Fields(Fld).Value
            .Update
        Next Fld
        
    End With
    UnPivot.MoveNext
 Loop Until UnPivot.EOF
 
 Pivot.Close
 UnPivot.Close
 Set Pivot = Nothing
 Set UnPivot = Nothing
 Set db = Nothing
 Set tbl = Nothing
End Sub

 

[alansidman]

Use Power Query to unpivot your data and then create a pivot table in Excel. No coding required. Can all be done in the U/I.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cities", type text}, {"Stores", type text}, {"6/13/2021", Int64.Type}, {"6/14/2021", Int64.Type}, {"6/15/2021", Int64.Type}, {"6/16/2021", Int64.Type}, {"6/17/2021", Int64.Type}, {"6/18/2021", Int64.Type}, {"6/19/2021", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Cities", "Stores"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Book1
	F	G	H	I
2	Stores	Cities	Attribute	Sum of Value
3	StoreA	Sydney	6/13/2021	228
4			6/14/2021	1524
5			6/15/2021	800
6			6/16/2021	1578
7			6/17/2021	1766
8			6/18/2021	1740
9			6/19/2021	556
10	StoreB	Melbourne	6/13/2021	1504
11			6/14/2021	1876
12			6/15/2021	1012
13			6/16/2021	744
14			6/17/2021	1608
15			6/18/2021	1818
16			6/19/2021	1346
17	StoreC	Perth	6/13/2021	950
18			6/14/2021	246
19			6/15/2021	601
20			6/16/2021	365
21			6/17/2021	115
22			6/18/2021	953
23			6/19/2021	470
24	StoreD	Adelaide	6/13/2021	910
25			6/14/2021	240
26			6/15/2021	731
27			6/16/2021	559
28			6/17/2021	405
29			6/18/2021	728
30			6/19/2021	318
31	StoreE	Brisbane	6/13/2021	534
32			6/14/2021	637
33			6/15/2021	389
34			6/16/2021	264
35			6/17/2021	785
36			6/18/2021	449
37			6/19/2021	541
Table1

 

[JonXL]

Do you need to have this in VBA? I think you might be able to get what you want with a crosstab query - no coding required.

Make summary data easier to read by using a crosstab query - Microsoft Support

Crosstab queries are useful for summarizing data, calculating statistics and then grouping the results.

support.microsoft.com

 

[EFANYoutube]

Use Power Query to unpivot your data and then create a pivot table in Excel. No coding required. Can all be done in the U/I.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cities", type text}, {"Stores", type text}, {"6/13/2021", Int64.Type}, {"6/14/2021", Int64.Type}, {"6/15/2021", Int64.Type}, {"6/16/2021", Int64.Type}, {"6/17/2021", Int64.Type}, {"6/18/2021", Int64.Type}, {"6/19/2021", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Cities", "Stores"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Book1
	F	G	H	I
2	Stores	Cities	Attribute	Sum of Value
3	StoreA	Sydney	6/13/2021	228
4			6/14/2021	1524
5			6/15/2021	800
6			6/16/2021	1578
7			6/17/2021	1766
8			6/18/2021	1740
9			6/19/2021	556
10	StoreB	Melbourne	6/13/2021	1504
11			6/14/2021	1876
12			6/15/2021	1012
13			6/16/2021	744
14			6/17/2021	1608
15			6/18/2021	1818
16			6/19/2021	1346
17	StoreC	Perth	6/13/2021	950
18			6/14/2021	246
19			6/15/2021	601
20			6/16/2021	365
21			6/17/2021	115
22			6/18/2021	953
23			6/19/2021	470
24	StoreD	Adelaide	6/13/2021	910
25			6/14/2021	240
26			6/15/2021	731
27			6/16/2021	559
28			6/17/2021	405
29			6/18/2021	728
30			6/19/2021	318
31	StoreE	Brisbane	6/13/2021	534
32			6/14/2021	637
33			6/15/2021	389
34			6/16/2021	264
35			6/17/2021	785
36			6/18/2021	449
37			6/19/2021	541
Table1

OP has an Access question?

 

[EFANYoutube]

Do you need to have this in VBA? I think you might be able to get what you want with a crosstab query - no coding required.

Make summary data easier to read by using a crosstab query - Microsoft Support

Crosstab queries are useful for summarizing data, calculating statistics and then grouping the results.

support.microsoft.com

The way the data is set up, crosstab won't work, thats why I believe OP wants to cleanse this data

 

[Guinaba]

OP has an Access question?

Thanks @alansidman but we have already done your suggestion, but the there is too much data to keep it in Excel.