Compute Quarterly Information

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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")));
 
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
 
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)
 
Back
Top