How do I establish a 12-month rolling sum function in access 2002.

G

Green

I am trying to creat an acess database which maintins a monthly total of
products as well as a 12 month rolling total. How do I create a rolling
total based upon date?
 
J

John Vinson

I am trying to creat an acess database which maintins a monthly total of
products as well as a 12 month rolling total. How do I create a rolling
total based upon date?
Click to expand...

Don't even bother trying to store totals or running totals in a Table.
That's the wrong place.

Instead, I'd use two tables, one for the productID and product name;
store the monthly values for each product in another table,
ProductSales, with the product ID and a datefield for the date of sale
(if it's a total for the month, just use the first of the month as the
date).

You can then create a Totals query summing the monthly sales as a
subquery:

SELECT Products.ProductID, Products.ProductName,
ProductSales.SalesDate, (SELECT Sum([Sales]) FROM ProductSales AS X
WHERE X.ProductID = Products.ProductID AND X.SalesDate >
DateAdd("yyyy", -2, ProductSales.SalesDate) AND X.SalesDate <=
ProductSales.SalesDate) AS RunningSum FROM Products INNER JOIN
ProductSales on Products.ProductID = ProductSales.ProductID;


John W. Vinson[MVP]
 
Top