Crosstab, Transform, or Pivot? I'm lost...

[OVH]

Trying to figure out the best way to turn my rows into columns, while maintaining the values and headers. Looks like a reverse crosstab query or something. I'm an amateur btw...

I have about 35 columns that I want to stay the same (keep as columns), then about 50 column that have months (Oct_2016, Nov_2016, etc.) across the top where I want the months as a single column with the value next to it.

Example below

Current table:

Industry	Customer	October	November	December	January	February	March	April	May
Finance	Cust1	1	1	1	1	1	1	1	1
Tech	Cust2	1	1	1	1	1	1	1	1
Consumer	Cust3	1	1	1	1	1	1	1	1

<tbody>
</tbody>

Future Table:

Industry	Customer	Date	Value
Finance	Cust1	October	1
Tech	Cust2	October	1
Consumer	Cust3	October	1
Finance	Cust1	November	1
Tech	Cust2	November	1
Consumer	Cust3	November	1
Finance	Cust1	December	1
Tech	Cust2	December	1
Consumer	Cust3	December	1
Finance	Cust1	January	1
Tech	Cust2	January	1
Consumer	Cust3	January	1
Finance	Cust1	February	1
Tech	Cust2	February	1
Consumer	Cust3	February	1
Finance	Cust1	March	1
Tech	Cust2	March	1
Consumer	Cust3	March	1
Finance	Cust1	April	1
Tech	Cust2	April	1
Consumer	Cust3	April	1
Finance	Cust1	May	1
Tech	Cust2	May	1
Consumer	Cust3	May	1

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Any idea how I can do this MS Access? Ideally using the designer type of UI vs. SQL/VBA, but I'm open to anything that works...

 

[OVH]

Thanks in advance for any help!

 

[sandy666]

you can try PowerQuery aka Get&Transform

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Industry", type text}, {"Customer", type text}, {"October", Int64.Type}, {"November", Int64.Type}, {"December", Int64.Type}, {"January", Int64.Type}, {"February", Int64.Type}, {"March", Int64.Type}, {"April", Int64.Type}, {"May", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Industry", "Customer"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

Industry

Customer

October

November

December

January

February

March

April

May

Industry

Customer

Attribute

Value

Finance

Cust1

1​

1​

1​

1​

1​

1​

1​

1​

Finance

Cust1

October

1​

Tech

Cust2

1​

1​

1​

1​

1​

1​

1​

1​

Finance

Cust1

November

1​

Consumer

Cust3

1​

1​

1​

1​

1​

1​

1​

1​

Finance

Cust1

December

1​

Finance

Cust1

January

1​

Finance

Cust1

February

1​

Finance

Cust1

March

1​

Finance

Cust1

April

1​

Finance

Cust1

May

1​

Tech

Cust2

October

1​

Tech

Cust2

November

1​

Tech

Cust2

December

1​

Tech

Cust2

January

1​

Tech

Cust2

February

1​

Tech

Cust2

March

1​

Tech

Cust2

April

1​

Tech

Cust2

May

1​

Consumer

Cust3

October

1​

Consumer

Cust3

November

1​

Consumer

Cust3

December

1​

Consumer

Cust3

January

1​

Consumer

Cust3

February

1​

Consumer

Cust3

March

1​

Consumer

Cust3

April

1​

Consumer

Cust3

May

1​

 

[OVH]

I don't see a powerquery function in Access. Am I missing something?

 

[xenou]

A crosstab query should work for Access, also. Did you try that?

 

[sandy666]

I don't see a powerquery function in Access. Am I missing something?

I missed Access category. Sorry