Query does not include specified expression

L

Love Buzz

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

John Spencer

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
 
L

Love Buzz

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.



John Spencer said:
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

Love said:
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] & "*"));
Click to expand...
Click to expand...
 
J

John Spencer

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

John said:
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

Love said:
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] & "*"));
Click to expand...
Click to expand...
 
L

Love Buzz

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.


John Spencer said:
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

John said:
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

Love said:
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] & "*"));
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

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

Love said:
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.


John Spencer said:
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

John said:
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

Love Buzz wrote:
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] & "*"));
Click to expand...
Click to expand...
Click to expand...
 
L

Love Buzz

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.

John Spencer said:
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

Love said:
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.


John Spencer said:
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

John Spencer wrote:
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

Love Buzz wrote:
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] & "*"));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Results multiplied by 3?? 2
Simple Select Query - To Complicated? 2
Cannot have aggregate function in expression 2
Average Calculation 1
Date criteria in query 6
Two Different Tables one Query 2
Access 2000 Query very slow compared to SQL Query Analyzer 5
Help with: ...query that does not include specified expression ....as part of an aggregate function. 3

Top