I guess I spoke too soon, all is not completely well but maybe Ken, Duane or someone else can advise. What was wrong was that I was trying to average a column of averages, causing the overflow problem. What I still need to figure out is how to get some type of average which can be used as a general guide of performance. Here is how I arrived at my average in the first place:
Incoming telephone lines each w/own measurements (example)
Line 1 = 89.2 speed of answer (ASA)and 59 calls handled
Line 2 - 41.7 ASA and 76 calls handled
Line 3 - 14.2 ASA and 992 calls handled
In order to get a weighted average, created an expression by multiplying the ASA x # calls handled for each line. In another query, took the sum from the expression above for the 3 lines and divided it by the sum of the total calls for a new ASA for all 3 lines combined, or weighted average.That all works fine.
This is a daily report, and the users would like to see an "Average" line at the end of each month to get a general idea of what is the norm. Since I can't average an average as in the example above, is there another way I can do this?
I can show you my SQl statements for both queries, although they are rather long.
Query 1
SELECT DailyAppl.FromDate, DailyAppl.ToDate, DailyAppl.TSF, DailyAppl.TotOffer, DailyAppl.TotHandled, DailyAppl.TotAband, DailyAppl.ASA, IIf([TotOffer]=0,0,[TotAband]/[TotOffer]) AS canc, [TSF]*[TotHandled]/100 AS Ans, [ASA]*[TotHandled] AS [As], DailyAppl.ATT, [TotOffer]*[WRAP] AS Aw, [ATT]*[TotHandled] AS At
FROM APPL_tblXref INNER JOIN DailyAppl ON APPL_tblXref.App = DailyAppl.App
GROUP BY DailyAppl.FromDate, DailyAppl.ToDate, DailyAppl.TSF, DailyAppl.TotOffer, DailyAppl.TotHandled, DailyAppl.TotAband, DailyAppl.ASA, DailyAppl.ATT, DailyAppl.WRAP, APPL_tblXref.App
HAVING (((APPL_tblXref.App) In (9,20,23,26,28,30,32,48,51,53,105,126,129,130,133,134,136,138,139,140,141,148,149,156,247,248,300)));
Query 2 (Report Query)
SELECT [CS DailyTot].ToDate, [SumOfAns]/[SumOfTotHandled] AS AvTSF, Sum([CS DailyTot].TotOffer) AS SumOfTotOffer, Sum([CS DailyTot].TotHandled) AS SumOfTotHandled, Sum([CS DailyTot].TotAband) AS SumOfTotAband, [SumofTotAband]/[SumOfTotOffer] AS PercentOfCanc, Sum([CS DailyTot].As) AS SumOfAs, [SumOfAs]/[SumOfTotHandled] AS ASA, Sum([CS DailyTot].Ans) AS SumOfAns, Sum([CS DailyTot].ATT) AS SumOfATT, [SumOfAw]/[SumOfTotOffer] AS Wrap, Sum([CS DailyTot].Aw) AS SumOfAw, [SumOfAt]/[SumOfTotHandled] AS ATT, Sum([CS DailyTot].At) AS SumOfAt
FROM [CS DailyTot]
GROUP BY [CS DailyTot].ToDate
HAVING ((([CS DailyTot].ToDate) Between #1/1/2004# And #12/31/2004#));
