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] & "*"));

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] & "*"));

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] & "*"));