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
|
|||
|
|||
Cannot have aggregate function in expression
Hello all.
Below is a query I am trying to create that contains operator productivity statistics. In addition, I would like to calculate the average time per day (for the period I select). The .csv file that I am using uses seconds, so I have to calculate to make the result hours. That works great, but I can't seem to get the average. I get the message I put in the subject line. Any assistance would be appreciated. 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(Sum([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
|
|||
|
|||
Cannot have aggregate function in expression
One implication of the SQL statement you provided is that you have a table
named "December 4". If this is the case, then you might also have tables named "December 5" and ... If THIS is the case, you are probably finding Access quite difficult to make do what you want. This is not Access' fault, since it expects well-normalized, relational data and your table structure is a spreadsheet! If the terms "relational" and "normalization" are not familiar, you and Access will probably have to work much harder to do simple things. Regards Jeff Boyce Microsoft Office/Access MVP "Love Buzz" wrote in message ... Hello all. Below is a query I am trying to create that contains operator productivity statistics. In addition, I would like to calculate the average time per day (for the period I select). The .csv file that I am using uses seconds, so I have to calculate to make the result hours. That works great, but I can't seem to get the average. I get the message I put in the subject line. Any assistance would be appreciated. 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(Sum([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] & "*")); |
#3
|
|||
|
|||
Cannot have aggregate function in expression
NM. I think I figured it out. Sorry to clupper up the forum.
"Love Buzz" wrote: Hello all. Below is a query I am trying to create that contains operator productivity statistics. In addition, I would like to calculate the average time per day (for the period I select). The .csv file that I am using uses seconds, so I have to calculate to make the result hours. That works great, but I can't seem to get the average. I get the message I put in the subject line. Any assistance would be appreciated. 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(Sum([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 | |
|
|