Prev: Format for text field
Next: How do I bring a filed from multiple records and join them tog
From: Cam on 14 Apr 2010 14:30
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
From: vanderghast on 14 Apr 2010 16:36
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


"Cam" <Cam(a)discussions.microsoft.com> wrote in message
news:F1ED6743-243E-48D8-9582-DF15F7BD3402(a)microsoft.com...
> 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

 | 
Pages: 1
Prev: Format for text field
Next: How do I bring a filed from multiple records and join them tog