Compute Quarterly Information

S

spartanmba

I am having trouble writting a query to compute the average quarterly cost
for the data set below.

MONTH COST
4 67
5 64
6 70
7 81
8 75
9 73
10 70
11 71
12 73


I would like the query to produce something like this.
Quarter Average Cost
Two 67
Three 76.33
Four 71.33

I am totally lost on where to even start on this. Thank you so much for
your assistance.

Best,
John
 
O

Ofer

Mybe there is a better way but you can try that

SELECT IIf([Month] In (1,2,3),"ONE",IIf([Month] In (4,5,6),"Two",IIf([Month]
In (7,8,9),"THREE","FOUR"))) AS Quarter, Avg(MyTable.Cost) AS AvgOfCost
FROM MyTable
GROUP BY IIf([Month] In (1,2,3),"ONE",IIf([Month] In
(4,5,6),"Two",IIf([Month] In (7,8,9),"THREE","FOUR")));
 
A

Alex H

If your month field is a date field you could group by format([date field],
"yyyyq") and then just perform an average on the Cost field

Alex
 
V

Van T. Dinh

SELECT ([T1].[MonthNo] + 2) \ 3 AS QuarterNo,
Avg([T1].[Cost]) AS AvgOfCost
FROM T1
GROUP BY ([T1].[MonthNo] + 2) \ 3

HTH
Van T. Dinh
MVP (Access)
 
Top