Hi, I am trying to create a query that will give me the total sales of each item col 1 based on 7 days, col 2 30 days, col 3 60 days etc.
My simple table has fields, "Item" "SaleDate" "Qty".
I did it with the crosstab query worked great using Date: Partition(Now()-[SaleDate],1,90,30) but i am only getting 30/60/90 and cant get the 7 day on the same query.
My other option was to filter them based on form text boxes using start dates, for ex I would have 3 boxes, and the query should return 3 columns each based on the end dates of the boxes, but I cannot figure out how to put in a criteria in the field.
This is my statement
?Any solutions
My simple table has fields, "Item" "SaleDate" "Qty".
I did it with the crosstab query worked great using Date: Partition(Now()-[SaleDate],1,90,30) but i am only getting 30/60/90 and cant get the 7 day on the same query.
My other option was to filter them based on form text boxes using start dates, for ex I would have 3 boxes, and the query should return 3 columns each based on the end dates of the boxes, but I cannot figure out how to put in a criteria in the field.
This is my statement
Code:
TRANSFORM Sum(SBSales.UnitsSold) AS SumOfUnitsSold
SELECT SBSales.SKU
FROM SBSales
GROUP BY SBSales.SKU
PIVOT Partition(Now()-[SaleDate],1,90,30);
?Any solutions
Last edited by a moderator: