Query does not include specified expression

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Love Buzz - 19 May 2008 16:56 GMT

Hello all.

My SQL view is below. Here is what I have. Two seperate tables that I am
trying to bring together. I have created two Queries and what I am trying to
do below is calculate the percentage of the employees errors compared to the
volume they have processed. I have joined the 'User ID' field together to
accomplish this. However, when I run the query, I get the 'You tried to
execute a query that does not include the specified expression (expr 4 below)
as part of an aggregate function.'

Any idea how to accomplish the result I am looking for? Thanks so much for
your help.

SELECT Errors.[User ID], Count(Errors.[Error Indicator]) AS [CountOfError
Indicator], [CountOfError Indicator]/[Individual Associate
Statistics]![SumOfItems] AS Expr4
FROM [Individual Associate Statistics] INNER JOIN Errors ON [Individual
Associate Statistics].UserID = Errors.[User ID]
WHERE (((Errors.[Notification Date]) Between [StartDate] And [EndDate] And
(Errors.[Notification Date]) Between [StartDate] And [EndDate] And
(Errors.[Notification Date]) Between [StartDate] And [EndDate]))
GROUP BY Errors.[User ID]
HAVING (((Errors.[User ID]) Like [Enter Employee User ID] & "*"));

Reply to this Message

John Spencer - 19 May 2008 17:19 GMT

SELECT Errors.[User ID]
, Count(Errors.[Error Indicator]) AS [CountOfError Indicator]
, Count(Errors.[Error Indicator])/[Individual Associate
Statistics]![SumOfItems] AS Expr4
FROM [Individual Associate Statistics] INNER JOIN Errors
ON [Individual Associate Statistics].UserID = Errors.[User ID]

WHERE Errors.[Notification Date]) Between [StartDate] And [EndDate]
AND Errors.[User ID] Like [Enter Employee User ID] & "*"

GROUP BY Errors.[User ID]
, Count(Errors.[Error Indicator])/
[Individual Associate Statistics]![SumOfItems]

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> Hello all.
>
[quoted text clipped - 19 lines]
> GROUP BY Errors.[User ID]
> HAVING (((Errors.[User ID]) Like [Enter Employee User ID] & "*"));

Reply to this Message

Love Buzz - 19 May 2008 20:06 GMT

Thanks John.

Unfortunatly I get a 'Cannot have aggregat function in GROUP BY clause
(Count([Errors].[Error Indicator])/[Individual Associate
Statistics]![SumOfItems])' message when trying to run the query.

> SELECT Errors.[User ID]
> , Count(Errors.[Error Indicator]) AS [CountOfError Indicator]
[quoted text clipped - 38 lines]
> > GROUP BY Errors.[User ID]
> > HAVING (((Errors.[User ID]) Like [Enter Employee User ID] & "*"));

Reply to this Message

John Spencer - 19 May 2008 20:28 GMT

Ok, then lets try this.

SELECT Errors.[User ID]
, Count(Errors.[Error Indicator]) AS [CountOfError Indicator]
, Count(Errors.[Error Indicator])/
First([Individual Associate Statistics]![SumOfItems]) AS Expr4
FROM [Individual Associate Statistics] INNER JOIN Errors
ON [Individual Associate Statistics].UserID = Errors.[User ID]

WHERE Errors.[Notification Date]) Between [StartDate] And [EndDate]
AND Errors.[User ID] Like [Enter Employee User ID] & "*"

GROUP BY Errors.[User ID]

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> SELECT Errors.[User ID]
> , Count(Errors.[Error Indicator]) AS [CountOfError Indicator]
[quoted text clipped - 39 lines]
>> GROUP BY Errors.[User ID]
>> HAVING (((Errors.[User ID]) Like [Enter Employee User ID] & "*"));

Reply to this Message

Love Buzz - 20 May 2008 00:23 GMT

Sorry for all of the trouble John. Although it ran the query without an
error message, the results were inaccurate.

It's weird because I can add the total number of errors by User ID for a
period using the following SQL:
SELECT Errors.[User ID], Errors.[Associate Name], Count(Errors.[Error
Indicator]) AS [CountOfError Indicator]
FROM Errors
WHERE (((Errors.[Notification Date]) Between [StartDate] And [EndDate]))
GROUP BY Errors.[User ID], Errors.[Associate Name];

But when I add another table to the query, because I want to compare the
number of errors by Associate to the number of items they actually processed
(for the same period), the number of errors comes back with a highly
exaggerated number.

Ahhh. Thanks for your help.

> Ok, then lets try this.
>
[quoted text clipped - 58 lines]
> >> GROUP BY Errors.[User ID]
> >> HAVING (((Errors.[User ID]) Like [Enter Employee User ID] & "*"));

Reply to this Message

John Spencer - 20 May 2008 12:36 GMT

Do you have more than one record per associate in the [Individual Associate
Statistics] table? If so, you will get multiple records for each error.

If multiple records is the case, then you need to get a unique list of
associates to match against the errors.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> Sorry for all of the trouble John. Although it ran the query without an
> error message, the results were inaccurate.
[quoted text clipped - 76 lines]
>>>> GROUP BY Errors.[User ID]
>>>> HAVING (((Errors.[User ID]) Like [Enter Employee User ID] & "*"));

Reply to this Message

Love Buzz - 20 May 2008 15:36 GMT

Good morning John.

Yes, there is more than one record per associate. So I created two queries
and one table of the Associate names and User IDs. Bringing those two
queries and one table together into another query did the trick.

Thanks for your guidance and patience. I really appreciate it.

> Do you have more than one record per associate in the [Individual Associate
> Statistics] table? If so, you will get multiple records for each error.
[quoted text clipped - 87 lines]
> >>>> GROUP BY Errors.[User ID]
> >>>> HAVING (((Errors.[User ID]) Like [Enter Employee User ID] & "*"));

Reply to this Message