How do I get rolling 3 monthly totals?

[jimgrin]

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.

 

[MichaelRed]

Jim,

You can do this in queries. You will need to do some work with the criteria expression, however the following shows a sample of getting the value fo one month. Since you only need three months, three of these queries (with suitable criteria) will give you all of the results. A union query (of these three) will then consolidate the results into a single 'record source'.

SELECT tblSales.Customer, Sum(tblSales.SaleVal) AS Sales, tblSales.Yr, Max(tblSales.Mnth) AS Month
FROM tblSales
WHERE (((tblSales.Mnth)=[MyMnth] Or (tblSales.Mnth)=[MyMnth]-1 Or (tblSales.Mnth)=[MyMnth]-2))
GROUP BY tblSales.Customer, tblSales.Yr;



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[jimgrin]

Thanks Michael, that was helpful, but as ever with brief descriptions of problems, there is always a "but".
Your method would be good if I only wanted the totals for three specific months. However, I have sales data going back to 99 and potentially going forward into the future, and I would like a 3 monthly rolling total for every month (i.e. present month and the two previous) for each Customer AND each month in the database.
The query needs to go through the whole set of data and do the sums for every month.
Is this possible? It makes my head hurt trying to think about it!

thanks, Jim.

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmmmmmmmmm,

I would probably use the same process, but elaborate it a bit.

"Assuming" you know (or can get) the beginning and ending months (incl the year). Set up the year as an additional parameter in the query. Make a "NEW" Table which has the fields which match this query. Modify the query to be an append query (to the NEW table - ref prev sentenance).

Make a delete * query for the NEW table.

Make a module to do this "stuffff". In the module, always run the delete query FIRST.

In the module, execute the the modified query for [First month + 2 & year] through the [Last month & Year], replacing the paraneters as appropiate.


The NEW table will have your results.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over