I have a cross tab query set up to display the quantity of products ordered each date.
I want the query to dynamically show only the future 7 days as columns. So the query would return:
ProductName, 10/06/2010, 11/06/2010, 12/06/2010.
TestProduct, 0, 10, 25
I've tried using the PIVOT IN command, but am not making progress.
This is what I have so far:
TRANSFORM Sum(qryOrdersWithDate.Quantity) AS SumOfQuantity
SELECT qryOrdersWithDate.ProductName AS Product
FROM qryOrdersWithDate
GROUP BY qryOrdersWithDate.ProductName
PIVOT Format([OrderDate],"Short Date");
Any help much appreciated
I want the query to dynamically show only the future 7 days as columns. So the query would return:
ProductName, 10/06/2010, 11/06/2010, 12/06/2010.
TestProduct, 0, 10, 25
I've tried using the PIVOT IN command, but am not making progress.
This is what I have so far:
TRANSFORM Sum(qryOrdersWithDate.Quantity) AS SumOfQuantity
SELECT qryOrdersWithDate.ProductName AS Product
FROM qryOrdersWithDate
GROUP BY qryOrdersWithDate.ProductName
PIVOT Format([OrderDate],"Short Date");
Any help much appreciated
