Crosstab or transpose query with multiple fields (2 Viewers)

reggied

New member
Local time
Today, 18:41
Joined
May 27, 2025
Messages
25
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 OrderCalendar WeekLDOWPrevious Friday Est RevenuePlanned Weekly RevenueActualRevenueCommitted PurchasesEst Purchases
1338/16/2539,842.1021,084.3010,974.582,169.542,319.17
2348/23/2559,124.0848,174.081,012.5020,933.53
3358/30/2555,781.4642,423.85
4369/6/2535,008.9064,910.9411,424.56
5379/13/2528,256.8019,306.80306.00
6389/20/2510,076.7015,982.604,436.395,936.19
7399/27/2523,050.2523,920.412,834.599,510.31
84010/4/256,680.9010,425.905,241.50

to look like this?
Week Order12345678
Calendar Week3334353637383940
Last Day of Week8/16/258/23/258/30/259/6/259/13/259/20/259/27/2510/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
 
Could be possible. Can you post a sample data set in Excel for us to play with?
 
What’s the table or query name?
Post the query SQL that you use and the results obtained.
 
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.
 
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?
 
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.
 
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.
 
Your output table does not reconcile with the input data.

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;
 
Last edited:

Users who are viewing this thread

Back
Top Bottom