Access to arrange the data from vertical to horizontal

[dhen21dx]

Hello,

I need advise on how can i do the sets of data from vertical to horizontal. here is the example below.

ID	Catergory	Descr
11111​	Sales ID	UUAD
11111​	Origin	US
11111​	PurVar	5​
11111​	Decr	Delta
22222​	Origin	TH
22222​	PurVar	7​
33333​	Sales ID	UUAT
33333​	Origin	DE
33333​	Decr	Delta_XR

Result:

ID	Sales ID	PurVar	Origin	Decr
11111​	UUAD	5​	US	Delta
22222​		7​	TH
33333​	UUAT		DE	Delta_XR

 

[dhen21dx]

By the way, i have a fixed columns as the result.

 

[petertenthije]

For this you can use a crosstab query. But this will only give reliable results when there is only one entry for each ID/Category combination!

Also, this is rather slow within Access. If you intend to export this to Excel at some point, you are really better of using Excel to do this!

Anyway: assume your data is listed in Table1:

TRANSFORM Max(Table1.Descr) AS MaxOfDescr
SELECT Table1.ID
FROM Table1
GROUP BY Table1.ID
PIVOT Table1.Catergory;

 

[alansidman]

You could also bring the data from Access into Power Query Editor and Pivot the data.

 

[dhen21dx]

Power Query Editor is within excel right?, can it be that i have a fixed category. I do have sometimes one category that is missing and i still do need to reflect it even though blanks

 

[Micron]

TRANSFORM Max(Table1.Descr) AS MaxOfDescr SELECT Table1.ID FROM Table1 GROUP BY Table1.ID PIVOT Table1.Catergory;

I tried that. The result leaves out a Descr value for 1111 (shown in sample as Delta) and I cannot figure out why. Any attempt I've made results in an error or this:
Query7 Query7

ID	Sales ID	PurVar	Origin	Decr
1111​	UUAD	5	US
2222​			TH	Delta
3333​	UUAT	7	DE	Delta_XR

2222 has no Delta value in the sample; 3333 has no PurVar of 7. When I copied table from forum I lost the ID values and had to type them in myself. I've left out digits for the IDs but that should not matter. I believe there are other errors as well, which no doubt are all related to the same cause.

 

[james_lankford]

I tried that. The result leaves out a Descr value for 1111 (shown in sample as Delta) and I cannot figure out why. Any attempt I've made results in an error or this:
Query7 ...

try again,
I am not familiar with these types of queries so I wanted to try it to learn
I went to access and did Create / Table
and pasted in the data of all 3 columns at once and then I edited the field names from F1, F2, F3 to their proper values
I ran the query and it worked as expected

"2222 has no Delta value in the sample; 3333 has no PurVar of 7"
the query put in an empty string (or maybe a null) for the missing values

 

[Micron]

Not sure why I'd try the same thing again and expect a different result. The only thing different that I can see is that I started with a spreadsheet that I imported and saved with a table name of dhen21dx so the actual sql is

TRANSFORM Max(dhen21dx.Descr) AS MaxOfDescr
SELECT dhen21dx.ID
FROM dhen21dx
GROUP BY dhen21dx.ID
PIVOT dhen21dx.Catergory;

My comment about no data in a record refers to the sample data in post 1, not the query results. If you review my result against the sample data I think you'll see what I mean (hopefully). I ended up with transformed data in a record where there should not be any. Gotta run for today.

 

[alansidman]

If you bring your data to PQ with this Mcode. You will of course need to change the source from the Current Workbook to your Access Table or Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Catergory", type text}, {"Descr", type any}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Catergory]), "Catergory", "Descr")
in
    #"Pivoted Column"

the result looks like this:

Book3
	A	B	C	D	E
1	ID	Sales ID	Origin	PurVar	Decr
2	11111	UUAD	US	5	Delta
3	22222		TH	7
4	33333	UUAT	DE		Delta_XR
Table1

 

[james_lankford]

Not sure why I'd try the same thing again and expect a different result. ..

because it works
I don't know why it didn't work the way you did it