Calculating quarters

[Spartacus]

Hello All!

I have data laid out as follows:

Measure MonthYr Value
Fixed Std Jan04 90%
Fixed Std Feb04 95%
-------- ------ ----
-------- ------ ----
Fixed Std Jun05 93%

My situation is two-fold: 1-I'm having difficulty coming up with a query that will calculate the weighted avg. of each quarter in the prior year and current year, 2-what to do about the current year's quarter when only 1 or 2 month's worth of data exists?

Can anyone help - it would be greatly appreciated. Thanks.

 

[WayneRyan]

Spartacus,

SELECT Table1.Measure, 
       Format([MeasureDate],"yyyyq") AS TheQuarter, 
       Avg(Table1.Percent) AS AvgOfPercent, 
       Count(Table1.Percent) AS SampleSize
FROM   Table1
GROUP BY Table1.Measure, 
         Format([MeasureDate],"yyyyq");

Wayne

 

[namliam]

My situation is two-fold: 1-I'm having difficulty coming up with a query that will calculate the weighted avg. of each quarter in the prior year and current year,
2-what to do about the current year's quarter when only 1 or 2 month's worth of data exists?

1) Use the query provided if its a date field... If not use CDate to convert it into a date (like 01-June-2005). Should be easy.

2) Is YOUR business rule... What do you do? Options:
a) Leave it out as beeing "incomplete"
b) Show it as beeing "current quarter up to last month" Thus "Incomplete"
c) Just show it and have users draw their own conclusions

Use the "samplesize" to work out A, B and/or C...

 

[MStef]

Hello Spartacus!
Look at "DemoAvQuarterA97.mdb",
I think it can help you. Maybe you need to adapt something.
Open Form1 and try.

(MStef alias Štef)