Crosstab or transpose query with multiple fields (1 Viewer)

[reggied]

I have been trying to figure out how to transpose a query per a customer request (they want to read the information horizontally instead of vertically). But I am struggling with the Crosstab query in access. Is there a way to transform the below data:

Week Order	Calendar Week	LDOW	Previous Friday Est Revenue	Planned Weekly Revenue	ActualRevenue	Committed Purchases	Est Purchases
1	33	8/16/25	39,842.10	21,084.30	10,974.58	2,169.54	2,319.17
2	34	8/23/25	59,124.08	48,174.08	1,012.50	20,933.53
3	35	8/30/25	55,781.46	42,423.85
4	36	9/6/25	35,008.90	64,910.94	11,424.56
5	37	9/13/25	28,256.80	19,306.80	306.00
6	38	9/20/25	10,076.70	15,982.60	4,436.39	5,936.19
7	39	9/27/25	23,050.25	23,920.41	2,834.59	9,510.31
8	40	10/4/25	6,680.90	10,425.90	5,241.50

to look like this?

Week Order	1	2	3	4	5	6	7	8
Calendar Week	33	34	35	36	37	38	39	40
Last Day of Week	8/16/25	8/23/25	8/30/25	9/6/25	9/13/25	9/20/25	9/27/25	10/4/25
Previous Friday Est Revenue	$39,842.10	$59,124.08	$55,781.46	$35,008.90	$28,256.80	$10,076.70	$23,050.25	$6,680.90
Planned Weekly Revenue	$21,084.30	$48,174.08	$42,423.85	$64,910.94	$19,306.80	$15,982.60	$23,920.41	$10,425.90
Actual Revenue	$10,974.58
Committed Purchases	$2,169.54	$1,012.50	$0.00	$0.00	$306.00	$4,436.39	$2,834.59	$0.00
Est Purchases	$2,319.17	$20,933.53	$0.00	$11,424.56	$0.00	$5,936.19	$9,510.31	$5,241.50

 

[theDBguy]

Could be possible. Can you post a sample data set in Excel for us to play with?

 

[isladogs]

What’s the table or query name?
Post the query SQL that you use and the results obtained.

 

[Mark_]

More important, do you have a change request (and payment) in hand? Access tends to be nice when you scroll vertically, but switching things to scroll horizontally isn't as straight forward.

 

[reggied]

Could be possible. Can you post a sample data set in Excel for us to play with?

 

[reggied]

Trying to post a .txt file

 

[MadPiet]

I wouldn't even think of trying this in Access. I'd maybe use PowerQuery and Transpose.

 

[reggied]

What’s the table or query name?
Post the query SQL that you use and the results obtained.

not sure what you mean by the query SQL. Unless you mean the query I use to get the original data?

 

[reggied]

I wouldn't even think of trying this in Access. I'd maybe use PowerQuery and Transpose.

I just tried and this and it looks like it would work well! But was hoping to find a solution that I can use within a front end of an access database.

Maybe from within the front end I can automate opening an excel file that already has the power query and data linked to the access database?

 

[MadPiet]

The way I would do it is to create a folder to keep the files you need to transpose in. Then I'd create a folder source in PowerQuery, Get the data, and do the transpose/cleanup/restructuring in PowerQuery (call it from VBA). then once that's done, run your canned append query from the attached excel file to your final table. I think you can use a pattern like {Open Excel File, Requery, wait til done} then transpose and import. I've done similar with data coming from PDFs through Excel.

 

[isladogs]

not sure what you mean by the query SQL. Unless you mean the query I use to get the original data?

You said you were struggling with the crosstab query in Access. I asked you to provide the SQL for your attempt at a crosstab query together with the query output.

 

[June7]

Your output table does not reconcile with the input data but I think the requirement is clear enough.

Rearrange data to normalize vertically. There is no builder/designer for UNION - must type or copy/paste in SQLView. Limit of 50 SELECT lines.

Query1: DataUNION
SELECT WeekOrder AS Wk, 1 AS Row, "CalWk" AS Cat, CalendarWeek AS Data FROM Data
UNION ALL SELECT WeekOrder, 2, "LDW", LDOW FROM Data
UNION ALL SELECT WeekOrder, 3, "PFER", PrevFriEstRev FROM Data
UNION ALL SELECT WeekOrder, 4, "PWR", PlanWklyRev FROM Data
UNION ALL SELECT WeekOrder, 5, "AR", ActRev FROM Data
UNION ALL SELECT WeekOrder, 6, "CP", ComPurch FROM Data
UNION ALL SELECT WeekOrder, 7, "EP", EstPurch FROM Data;

Use Query1 in a CROSSTAB.

Query2:
TRANSFORM Min(DataUNION.Data) AS MinOfData
SELECT DataUNION.Row, DataUNION.Cat
FROM DataUNION
GROUP BY DataUNION.Row, DataUNION.Cat
PIVOT DataUNION.Wk;

Is the posted input dataset result of a query? If I knew raw data structure and how you generate data in your query, might be able to eliminate it to build UNION. You could attach db for review.

 

[MarkK]

Use a report instead. Resize all the Textboxes so they are tall and narrow and then set them all to TextBox.IsVertical = True. Reverse the column order, print it, and hand it to the customer rotated 90 degrees.

 

[MarkK]

No, seriously.

 

[reggied]

You said you were struggling with the crosstab query in Access. I asked you to provide the SQL for your attempt at a crosstab query together with the query output.

Honestly, I didn't get too far with the Crosstab because it limited me to only 3 fields to use for Row headings.

 

[reggied]

Use a report instead. Resize all the Textboxes so they are tall and narrow and then set them all to TextBox.IsVertical = True. Reverse the column order, print it, and hand it to the customer rotated 90 degrees.

I would totally do this as a solution but I am trying to create a form/report on the screen the user can view. Not sure how to rotate the screen!

 

[reggied]

Your output table does not reconcile with the input data but I think the requirement is clear enough.

Rearrange data to normalize vertically. There is no builder/designer for UNION - must type or copy/paste in SQLView. Limit of 50 SELECT lines.

Query1: DataUNION
SELECT WeekOrder AS Wk, 1 AS Row, "CalWk" AS Cat, CalendarWeek AS Data FROM Data
UNION ALL SELECT WeekOrder, 2, "LDW", LDOW FROM Data
UNION ALL SELECT WeekOrder, 3, "PFER", PrevFriEstRev FROM Data
UNION ALL SELECT WeekOrder, 4, "PWR", PlanWklyRev FROM Data
UNION ALL SELECT WeekOrder, 5, "AR", ActRev FROM Data
UNION ALL SELECT WeekOrder, 6, "CP", ComPurch FROM Data
UNION ALL SELECT WeekOrder, 7, "EP", EstPurch FROM Data;

Use Query1 in a CROSSTAB.

Query2:
TRANSFORM Min(DataUNION.Data) AS MinOfData
SELECT DataUNION.Row, DataUNION.Cat
FROM DataUNION
GROUP BY DataUNION.Row, DataUNION.Cat
PIVOT DataUNION.Wk;

Is the posted input dataset result of a query? If I knew raw data structure and how you generate data in your query, might be able to eliminate it to build UNION. You could attach db for review.

Good catch about the output table not matching the input. I pasted from an excel sheet and for some reason the data was slightly re-arranged.

Will give this a shot!

 

[reggied]

Your output table does not reconcile with the input data but I think the requirement is clear enough.

Rearrange data to normalize vertically. There is no builder/designer for UNION - must type or copy/paste in SQLView. Limit of 50 SELECT lines.

Query1: DataUNION
SELECT WeekOrder AS Wk, 1 AS Row, "CalWk" AS Cat, CalendarWeek AS Data FROM Data
UNION ALL SELECT WeekOrder, 2, "LDW", LDOW FROM Data
UNION ALL SELECT WeekOrder, 3, "PFER", PrevFriEstRev FROM Data
UNION ALL SELECT WeekOrder, 4, "PWR", PlanWklyRev FROM Data
UNION ALL SELECT WeekOrder, 5, "AR", ActRev FROM Data
UNION ALL SELECT WeekOrder, 6, "CP", ComPurch FROM Data
UNION ALL SELECT WeekOrder, 7, "EP", EstPurch FROM Data;

Use Query1 in a CROSSTAB.

Query2:
TRANSFORM Min(DataUNION.Data) AS MinOfData
SELECT DataUNION.Row, DataUNION.Cat
FROM DataUNION
GROUP BY DataUNION.Row, DataUNION.Cat
PIVOT DataUNION.Wk;

Is the posted input dataset result of a query? If I knew raw data structure and how you generate data in your query, might be able to eliminate it to build UNION. You could attach db for review.

OK, this looks like it could be the solution! I tried this and it worked fine. But I actually have 3 more fields (It's the PlanWeklyRev broken up into 3 categories). The union query worked fine when I added the 3 fields. But when I ran the Crosstab query I got an error message about resources being exceeded.

I tried adding one field at a time. It works with only adding 1 more field; but after adding the 2nd field Access gave me the "Query is too complex" error. what to do?

 

[isladogs]

You are only limited to 3 fields using the crosstab wizard. Start with that then add all additional fields manually in the query SQL or query designer window

 

[Gasman]

Not sure how to rotate the screen!

To rotate the screen in Windows 11, you can either use keyboard shortcuts or access the display settings through the Settings app. The keyboard shortcut Ctrl + Alt + Up Arrow will return the screen to the default landscape orientation. Other shortcuts like Ctrl + Alt + Right Arrow and Ctrl + Alt + Left Arrow will rotate the screen 90 degrees to the right and left respectively. If the shortcuts don't work, you can go to Settings > System > Display, and under the "Scale & layout" section, change the "Display orientation" to your preferred setting.