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?

 

[Guinaba]

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

THANKS a lot @EFANYoutube!! There is just one issue, when I import the data, Access automatically creates a new column F10 into my Unpivot table (as last col), so when I run the code I get the error:
Run-time error '13' -- Type mismatch. the sub is stopping at that point.

If I delete that column F10 I get the error below.

Run-time error '2391'
Field 'F10' doesn't exist in destination table 'T_Unpivot.'

 

[alansidman]

You can bring the data directly into PQ from Access. No need to bring it into Excel. Once you unpivot the data move it directly to Power Pivot and do your Pivot Table there.

 

[EFANYoutube]

THANKS a lot @EFANYoutube!! There is just one issue, when I import the data, Access automatically creates a new column F10 into my Unpivot table (as last col), so when I run the code I get the error:
Run-time error '13' -- Type mismatch. the sub is stopping at that point.

View attachment 41403

If I delete that column F10 I get the error below.

Run-time error '2391'
Field 'F10' doesn't exist in destination table 'T_Unpivot.'

Ok
If its always "F10" then add the following (Don't delete F10)

        For Fld = 2 To UnPivot.Fields.Count - 1
           If UnPivot.Fields(Fld).Name <> "F10" Then
            .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
           End If
        Next Fld

 

[Guinaba]

Ok
If its always "F10" then add the following (Don't delete F10)

        For Fld = 2 To UnPivot.Fields.Count - 1
           If UnPivot.Fields(Fld).Name <> "F10" Then
            .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
           End If
        Next Fld

Thanks @EFANYoutube! It works perfectly now.