Calculate a moving average query

[Lou Neuder]

I have a query with two simple fields: Date and Milk
(daily milk production). I would like to query that
query so that I can have a third field that calculates a
moving or rolling average of Milk based on the last 3
days. I tried making a function module that calculates
moving average but am not being successful.

Date Milk Milk3Avg
**** **** ********
1/1/04 58
1/2/04 60
1/3/04 62 60
1/4/04 61 61
1/5/04 59 60.67
1/6/04 62 60.67
1/7/04 65 62

Help!

Dr. Lou Neuder
Green Meadow Farms, Inc.
(e-mail address removed)

 

[John Viescas]

You could try something like this:

SELECT [Date], Milk,
(SELECT Avg(Milk)
FROM (SELECT Top 3 Milk
FROM MyTable As T2
WHERE T2.[Date] <= MyTable.Date
ORDER BY T2.[Date] Desc) As A2) As Milk3Avg
FROM MyTable
ORDER BY MyTable.[Date] Asc;

The above is valid ANSI-SQL, but JET might not like the outer reference two
levels deep.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[Zac]

I get a data entry dialog for Mytable.Date when I run