Thanks for your suggestion, Michael, and also for the interesting point you
made about taking averages of subsets.
You are absolutely right that in general, the average of the averages will
produce a different result than taking an direct average of all the members
of the subset. However, there is one set of cases where the two averages
are always equal - when the number of elements in each subset are equal.
Using the elements from your example,
Average(1,1,1) = 1
Average(2,2,2) = 2
and both the average of the averages and the direct average of the subset
yields 1.5.
In the case of my query, that's exactly what I'm doing. In all cases, I'm
taking the average of four quarters, and my rolling averages also takes the
average of the four most recent averages.
Prior to reading your message this afternoon, I had managed to design a
two-query solution that produces the desired result:
Query 1: qryDaysToApprove
SELECT tblApplication.Action, tblApplication.InstitutionType,
DateDiff("d",[Filed],[Approval]) AS TotalDays, IIf(Not
IsNull([Reactivated]),DateDiff("d",[Suspended],[Reactivated]),0) AS
SuspendedDays, [TotalDays]-[SuspendedDays] AS ApprovalDays,
tblApplication.Applicant, tblApplication.Filed, tblApplication.Approval,
tblApplication.Suspended, tblApplication.Reactivated
FROM tblApplication
WHERE (((tblApplication.Action)="approved") AND
((tblApplication.InstitutionType)="Bank" Or
(tblApplication.InstitutionType)="Industrial Bank") AND
((DateDiff("d",[Filed],[Approval]))>0) AND
((tblApplication.Application)="new charter"))
ORDER BY tblApplication.Approval;
Query 2: qryQtrAverage
SELECT DatePart("yyyy",[Approval]) & "-Q" & DatePart("q",[Approval]) AS
Quarter, Avg(qryDaysToApprove.ApprovalDays) AS AvgApproval,
Val(IIf([quarterID]>3,DAvg("AvgApproval","qryQtrAverage","quarterID <= " &
[quarterID] & " AND quarterID >= " & [quarterID]-3),"")) AS 4QtrAvg,
Count(qryDaysToApprove.Action) AS CountOfAction,
First(DLookUp("quarterID","tblQuarterID","quarterTxt = '" & [Quarter] &
"'")) AS quarterID
FROM qryDaysToApprove
WHERE ((Not (qryDaysToApprove.Approval) Is Null))
GROUP BY DatePart("yyyy",[Approval]) & "-Q" & DatePart("q",[Approval])
ORDER BY DatePart("yyyy",[Approval]) & "-Q" & DatePart("q",[Approval]);
I tested these two queries over several different data sets, and it works
just fine. However, I'm also going to try out your suggestion as an
alternate approach and learning experience. Your use of the DateSerial()
function is interesting, and it looks like something that would be of great
help with some other queries that I'm trying to build.
Again, thank you for taking the time to produce a creative approach for
handling dates in queries, and getting me to take a look at what looks like
a powerful new (to me) function.
Paul