Running Avg using recent 3 months

[NoobHere]

I need a query that will give me a running 3 month average.

Right now I was able to get as far as a query that will give me a running
average. What do I need to do to so that it reduces it to a running 3 month
average.

Background:

User can access the query for a date range (dynamic). The query will give
results for each instance per month and an average. However the average will
need to pull a running 3 month average.

Table Sample
Name YearMonth Total
A 2008-01 5
B 2008-02 6
C 2008-03 19
D 2008-04 2
E 2008-05 29

Query results would need to look like the following:

Name YearMonth Total Avg
A 2008-01 5
B 2008-02 6
C 2008-03 19 10
D 2008-04 2 9
E 2008-05 29 16.66

Right now all I get is: -my running average effort-
Name YearMonth Total Avg
A 2008-01 5 5
B 2008-02 6 5.5
C 2008-03 19 10
D 2008-04 2 8
E 2008-05 29 12.2

I'm pretty new to this so any help is definitely appreciated.

Thanks in advance!

 

[Maarkr]

something like this:
SELECT Measures.MoYr, Avg(Measures.Use) AS AvgOfUse
GROUP BY Measures.MoYr
HAVING (((Measures.MoYr)>=DateSerial(Year(Date()),Month(Date())-3,1));

so you set up your aggregate query (click the using the Sum character in
the toolbar) with AVG in the Total: row of the query...
Use the portion of the DateSerial() lingo above in the criteria of your
numerical data...the -3 means look back 3 months; the 1 indicates the first
of the month.

 

[Ken Sheridan]

You'll need to use subqueries for this. The following is an example which
returns the total sales per product per month, and the average total monthly
sale figure per product over the current and previous two months. Its
necessary to return the averages in this case by dividing the SUM by 3 as its
not possible to average the sums in one operation using the AVG operator. It
differs from your example in that includes the previous two and one months
before the start of the range in computing the average monthly sales for the
first and second months in the range. As the query is unrestricted these
will have zero sales of course, but if it were to be restricted by date
parameters then the sales for the 2/1 months before the first month would be
taken into account in computing the averages for the first two months. As a
query of this type would normally be used as the source for a report it would
be a simple task to suppress the first two months averages in the report's
output.

SELECT DISTINCT Product, YEAR([SaleDate]) AS SaleYear, MONTH([SaleDate]) AS
SaleMonth,
(SELECT SUM([UnitPrice]*[Quantity])
FROM Sales AS S3
WHERE S3.Product = S1.Product
AND YEAR(S3.[SaleDate]) = YEAR(S1.[SaleDate])
AND MONTH(S3.[SaleDate])= MONTH(S1.[SaleDate]))
AS TotalMonthlySales,
(SELECT SUM([UnitPrice]*[Quantity])
FROM Sales AS S2
WHERE S2.Product = S1.Product
AND [SaleDate] BETWEEN
DATEADD("m",-2,DATESERIAL(YEAR(S1.[SaleDate]),MONTH(S1.[SaleDate]),1))
AND
DATEADD("m",1,DATESERIAL(YEAR(S1.[SaleDate]),MONTH(S1.[SaleDate]),0)))/3
AS ThreeMonthAvgMonthlySale
FROM Sales AS S1
ORDER BY Product, YEAR([SaleDate]),MONTH([SaleDate]);

Ken Sheridan
Stafford, England