DAVG in Query

[Scooter]

I have a query (DepTotalEffAvg) that returns the following data:

Week Department Average Efficiency
1 90.75
2 69.79
3 99.49
4 88.71
5 75.94

For each week, I need to calculate the 4-week average efficiency. So,
the data should be:

Week Department Average Efficiency 4-Week Average Efficiency
1 90.75 90.75
2 69.79 80.27
3 99.49 86.68
4 88.71 87.19
5 75.94 83.48

I tried using the following statement in a separate query
(DepTotalEff4Week):

SELECT T3.Week, T3.[Department Average Efficiency], DAvg("[Department
Average Efficiency]","[DepTotalEffAvg]","[Week] BETWEEN " & ([Input
Week]-3) & " AND " & [Input Week] & "") AS [4-Week Average Efficiency]
FROM DepTotalEffAvg AS T3
ORDER BY T3.Week;

"Input Week" is from a text box on a form.

When I run the query (based on "Input Week" = 5), it returns

Week Department Average Efficiency 4-Week Average Efficiency
1 90.75 83.48
2 69.79 83.48
3 99.49 83.48
4 88.71 83.48
5 75.94 83.48

It's averaging correctly, but the average is based on week 2-5 only,
and it's placing that value in every row. I need the 4-week average
for each row.

Any suggestions?

 

[Marshall Barton]

SELECT T3.Week, T3.[Department Average Efficiency], DAvg("[Department
Average Efficiency]","[DepTotalEffAvg]","[Week] BETWEEN " & ([Input
Week]-3) & " AND " & [Input Week] & "") AS [4-Week Average Efficiency]
FROM DepTotalEffAvg AS T3
ORDER BY T3.Week;

I think this is what you want:

SELECT T3.Week, T3.[Department Average Efficiency],
DAvg("[Department Average Efficiency]",
"[DepTotalEffAvg]",
"[Week] BETWEEN " & (T3.Week-3) &
" AND " & T3.Week & "")
AS [4-Week Average Efficiency]
FROM DepTotalEffAvg AS T3
WHERE T3.Week <= [Input Week]
ORDER BY T3.Week;