Hi,
I have a problem with a query which is based on a crosstab query.
First of all, the SQL code from the crosstab query is as follows:
TRANSFORM Count([FG-SalesOrders].QTY) AS CountOfQTY
SELECT [FG-SalesOrders].ITEMID
FROM [FG-SalesOrders]
GROUP BY [FG-SalesOrders].ITEMID
PIVOT Format([Dateexpected],"yyyy/mm");
This splits my transactional data into one column with Item numbers and several columns with date intervals (e.g. 2009-11, 2009-12, 2010-01 etc.).
I have an Make-Table query where I would like to generate the following columns:
Item number
Februar sales orders
March sales orders
April sales orders
May sales orders
June sales orders
July sales orders
(Last five months sales including current month)
and also some other information from another crosstab query with production orders also grouped by months as the above. The end result should therefore be produced in a new table with the following headings, when this is run in July:
Item number
Februar sales orders
March sales orders
April sales orders
May sales orders
June sales orders
July sales orders
Februar Production orders
March Production orders
April Production orders
May Production orders
June Production orders
July Production orders
I have found a tutorial of how to produce these dynamic headings if it should be in a report (by using DateAdd). But this is a make-table query since I need the data for further analysis.
Does anyone have a solution for this?
It would be very much appreciated - thank you!
Best Regards
Jess Pedersen