I have a database of sales info. with one record for each sale (customer, date, product etc.). I somehow need to produce a query or report to give me for each customer and each month, a rolling total of the last 3 months. So I have:
Customer YearMonth SaleVal
Cust1 2000 01 200
Cust1 2000 02 100
Cust1 2000 03 300
Cust1 2000 04 100
And I want:
Customer YearMonth 3MonthTotal
Cust1 2000 03 600
Cust1 2000 04 500
To clarify, for April, the sum is Apr+Mar+Feb. I thought I could do this just with queries, but now I am not so sure. I tried to do a complicated sum in the "Total" row of a query but it didn't work.
The only way I can see is either to use VB to step through the records, or to do the total in a report somehow.
Any suggestions (general or specific) greatly appreciated,
Jim.
Customer YearMonth SaleVal
Cust1 2000 01 200
Cust1 2000 02 100
Cust1 2000 03 300
Cust1 2000 04 100
And I want:
Customer YearMonth 3MonthTotal
Cust1 2000 03 600
Cust1 2000 04 500
To clarify, for April, the sum is Apr+Mar+Feb. I thought I could do this just with queries, but now I am not so sure. I tried to do a complicated sum in the "Total" row of a query but it didn't work.
The only way I can see is either to use VB to step through the records, or to do the total in a report somehow.
Any suggestions (general or specific) greatly appreciated,
Jim.