How to calculate average for current quarter?

[Cam]

Hello,

I have a table with field Year, Month, date, model, value. What would be the
formula to calculate the current quarter average for the different model?

Sample:
Year Month Date Model Value
2010 1 1/1/10 A 20
2010 2 2/1/10 A 14
2010 3 3/1/10 A 10
2010 4 4/1/10 A 6
2010 5 5/1/10 B 14
2010 6 6/1/10 B 6

Result:
Year Qtr Model Value
2010 2 A 6
2010 2 B 10

 

[vanderghast]

SELECT Year([date]), DatePart("q", [date]), model, Avg([value])
FROM table
GROUP BY Year([date]), DatePart("q", [date]), model


should do.


Note that it assumes you have only one record per model per month (per
year), else, the averaging computation would be wrong...


Vanderghast, Access MVP