Average Calculation

[Love Buzz]

Hi all.

Please see my SQL below.

I am pulling data from a Table (December 4). The query I am building is
working great except for the average (Exp4) timeinqueu. About half of the
average timeinqueue is correct with the other providing me 50% of the result
and in one case 33%.

This makes absolutely no sense to me, but I am a newb and that is
understandable.

Any assistance you can provide would be helpful.

SELECT [December 4].UserID, [December 4].Queu, [December 4].Function,
Sum([December 4].Items) AS SumOfItems, Sum([December 4].timeinqueu) AS
SumOftimeinqueu, Sum([December 4]![timeinqueu]/"60"/60) AS Expr1, [Queus and
SLAs].SLA, [SumOfItems]/[Expr1] AS Expr2, [Expr2]/[SLA] AS Expr3,
Avg([December 4]![timeinqueu]/"60"/60) AS Expr4
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON ([December
4].Queu = [Queus and SLAs].[Queu Number]) AND ([December 4].Function = [Queus
and SLAs].Function)
WHERE ((([December 4].Date) Between [StartDate] And [EndDate] And ([December
4].Date) Between [StartDate] And [EndDate] And ([December 4].Date) Between
[StartDate] And [EndDate]))
GROUP BY [December 4].UserID, [December 4].Queu, [December 4].Function,
[Queus and SLAs].SLA
HAVING ((([December 4].UserID) Like [Enter Employee User ID] & "*"));

 

[Jeff Boyce]

What type of data is [timeinqueu]?

How are you handling nulls and/or zero-length strings in your data? I
suspect they are affecting the "average".

Regards

Jeff Boyce
Microsoft Office/Access MVP