If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Average Calculation
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] & "*")); |
#2
|
|||
|
|||
Average Calculation
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 "Love Buzz" wrote in message ... 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] & "*")); |
Thread Tools | |
Display Modes | |
|
|