Rolling 12 month view of data viewed by month

[McGregor]

Hi Everyone,I have a database of customer purchases. The fields that i need to work with are:CustomerNumber, Date, Product, Qty and cost. I update this DB at the beginning of the new month with the previous months purchase data.

I can do a query tnat will give me the individual months totals for each customer by month for a 12 month view. This involves go to the criteria and/or expressions and manually changing the date criteria.

Is there a way that once I have set the date for the most recent month (i.e. Last Month - July), that the criteria for the previous 11 months will be reset (rolled forward by 1 month)? Maybe there is another way of performing this function?

Best Regards
Greg

 

[Databaseguy]

This will always return a years worh of records. This goes in your date field

>now()-365.

 

[McGregor]

Thanks Databaseguy. I need to view the total sales for each of the individual months over the last 12 months.

 

[Databaseguy]

CrosstabQuery

TRANSFORM Sum([Qty]*[Cost] AS NewFieldName
SELECT CustomerNumber,Product
ROM YourTable
WHERE ((([Date])>Now()-365))
GROUP BY CustomerNumber,Product
PIVOT DatePart("m",[Date]);

 

[McGregor]

Thanks Databaseguy. I will try it out next week when I am back in office!