How do I create a rolling average of monthly data in an access.

[Guest]

I have a database of various information organized by month. I need to do a
rolling average of these values for each month. The rolling average is
defined as the total of the last 12 months divided by 12. While I have found
annual and quarterly average methods, these do not cross over years to
provide a rolling average. I then need put this rolling average on a report
with the data. I have the report built, but I am unable to figure out how to
do a rolling avergae in access. I have easily done this in excel, but I was
asked to build this information in access.

 

[Allen Browne]

Firstly, you will need a query that collects the monthly totals.

This example works with the Northwind sample database.
Create a new query. Paste in the following.
Switch to Design view (View menu) to see what's going on.

SELECT DateSerial(Year([Orders].[OrderDate]),Month([Orders].[OrderDate]),1)
AS ForMonth,
Sum(CCur(Nz([Order Details].[Quantity]*[Order Details].[UnitPrice],0))) AS
MonthAmount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY
DateSerial(Year([Orders].[OrderDate]),Month([Orders].[OrderDate]),1);

You can now use a subquery to get the rolling average:
1. Save the query above with the name "a". Close.
2. Create another query, using query "a" as an input "table".
3. Drag the 2 fields into the grid.
4. In the 3rd column of the field row, enter:

RollingAvg: (SELECT Avg(Dupe.MonthAmount) AS AvgOfMonthAmount
FROM a AS Dupe
WHERE Dupe.ForMonth Between DateAdd("m", -11, a.ForMonth) And a.ForMonth )