Calculating Three Different Rolling Averages in One Query

[LadyDi]

Is there a way to calculate three different rolling averages in one query?

I just inherited a database where someone is using three queries to capture the same information only with different time frames. They were calculating a rolling three month average, six month average, and twelve month average. I would like to combine these queries into one to reduce time spent running reports from the database. All three queries are based on one table. One of the columns in that table is called "Month Start Date". That field shows the first day of the month when a call was entered. I can get the query to tell me the first month in the three month period and the first month in the six month period, but I can't get it to calculate the averages of the calls that fall in those time frames. Here is the SQL for the query I have now. When I try to run this, I get the error message that my formula is not part of an aggregate function.

SELECT DISTINCT DateAdd('m','-2',(Max([Month Start Date]))) AS ThreeMonthStartDate, DateAdd('m','-5',(Max([Month Start Date]))) AS SixMonthStartDate, Max([Month Start Date]) AS MaxStartDate, IIf([Month Start Date] Between [ThreeMonthStartDate] And [MaxStartDate],Avg([All Call Rate]),' ') AS ThreeMonthAverageCallRate, LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num]
FROM LIST_WITH_TNC;

Is there a way to make this work? Any suggestions you have would be greatly appreciated.

 

[LadyDi]

Okay, I tried making a separate query to hold the dates I wanted to use to determine the three month period and the six month period. I then re-wrote my query like this:

SELECT LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num], Round(IIf([LIST_WITH_TNC]![Month Start Date] Between [ThreeMonthStartDate] And [MaxStartDate],Avg([All Call Rate]),' '),3) AS AvgCallRate
FROM LIST_WITH_TNC, StartDatesQuery
GROUP BY LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num];

It still doesn't work. Now I get the message that I'm "trying to execute a query that does not include the specified expression". Is it not possible to do an "AverageIf" statement in Access like you can do in Excel? That's all I want to do. If the Month Start Date is within the three month period, I want the average of the call rate. The same with the six month period. I would like to list them across so it would look something like this:

Device______Model_____ThreeMonthAvg____SixMonthAvg
VCR_______Samsung_______0.56___________0.23
TV________Zenith_________0.15___________0.03

Does this have to be done in three different queries? :banghead:

 

[way2bord]

Okay, I tried making a separate query to hold the dates I wanted to use to determine the three month period and the six month period. I then re-wrote my query like this:

SELECT LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num], Round(IIf([LIST_WITH_TNC]![Month Start Date] Between [ThreeMonthStartDate] And [MaxStartDate],Avg([All Call Rate]),' '),3) AS AvgCallRate
FROM LIST_WITH_TNC, StartDatesQuery
GROUP BY LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num];

It still doesn't work. Now I get the message that I'm "trying to execute a query that does not include the specified expression". Is it not possible to do an "AverageIf" statement in Access like you can do in Excel? That's all I want to do. If the Month Start Date is within the three month period, I want the average of the call rate. The same with the six month period. I would like to list them across so it would look something like this:

Device______Model_____ThreeMonthAvg____SixMonthAvg
VCR_______Samsung_______0.56___________0.23
TV________Zenith_________0.15___________0.03

Does this have to be done in three different queries? :banghead:

If the initial 3 queries work, why change it?
Also - could you provide all 3 working queries?
And your two recordsets aren't joined - is this a problem?