does not include the specified exp as part of an aggregate

G

Guest

I have a query and it returns what's expected:

SELECT tblComplaints.DateReceived, tblComplaints.ProductionFacility
FROM tblFacilities INNER JOIN tblComplaints ON tblFacilities.txtFacilityID =
tblComplaints.ProductionFacility
WHERE (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
((tblComplaints.ProductionFacility)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
(([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));

I'm trying to count the total number of records and so I've added:
SELECT tblComplaints.DateReceived, tblComplaints.ProductionFacility,
Count([ProductionFacility]) AS TotalComps
FROM tblFacilities INNER JOIN tblComplaints ON tblFacilities.txtFacilityID =
tblComplaints.ProductionFacility
WHERE (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
((tblComplaints.ProductionFacility)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
(([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));

This returns an error regarding [DateReceived]:
(Error 3122) You tried to execute a query that does not include the
specified expression as part of an aggregate function or grouping.

Possible cause:

You did not enter an aggregate function in the TRANSFORM statement.

I can't make heads or tails of this. What am I doing wrong?

Thanks for your help!
 
J

John Spencer

WHEN you add an aggregate function to a query, you must either add
aggreagate functions to all the other fields in the select clause or group
by those fields.

SELECT tblComplaints.DateReceived, tblComplaints.ProductionFacility,
Count([ProductionFacility]) AS TotalComps
FROM tblFacilities INNER JOIN tblComplaints ON tblFacilities.txtFacilityID =
tblComplaints.ProductionFacility
WHERE (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
((tblComplaints.ProductionFacility)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
(([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null))
GROUP BY tblComplaints.DateReceived, tblComplaints.ProductionFacility

That will give you a count by facility by date - one count for each
combination.

If you don't want the count for each combination, you will need to remove a
field (or fields) from both the select clasuse and the group by clause.



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

JohnLute said:
I have a query and it returns what's expected:

SELECT tblComplaints.DateReceived, tblComplaints.ProductionFacility
FROM tblFacilities INNER JOIN tblComplaints ON tblFacilities.txtFacilityID
=
tblComplaints.ProductionFacility
WHERE (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
((tblComplaints.ProductionFacility)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
(([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));

I'm trying to count the total number of records and so I've added:
SELECT tblComplaints.DateReceived, tblComplaints.ProductionFacility,
Count([ProductionFacility]) AS TotalComps
FROM tblFacilities INNER JOIN tblComplaints ON tblFacilities.txtFacilityID
=
tblComplaints.ProductionFacility
WHERE (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
((tblComplaints.ProductionFacility)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
(([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));

This returns an error regarding [DateReceived]:
(Error 3122) You tried to execute a query that does not include the
specified expression as part of an aggregate function or grouping.

Possible cause:

You did not enter an aggregate function in the TRANSFORM statement.

I can't make heads or tails of this. What am I doing wrong?

Thanks for your help!
Click to expand...
 
G

Guest

Thanks, John!

That should've been clear to me! It flew right over my head.

--
www.Marzetti.com


John Spencer said:
WHEN you add an aggregate function to a query, you must either add
aggreagate functions to all the other fields in the select clause or group
by those fields.

SELECT tblComplaints.DateReceived, tblComplaints.ProductionFacility,
Count([ProductionFacility]) AS TotalComps
FROM tblFacilities INNER JOIN tblComplaints ON tblFacilities.txtFacilityID =
tblComplaints.ProductionFacility
WHERE (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
((tblComplaints.ProductionFacility)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
(([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null))
GROUP BY tblComplaints.DateReceived, tblComplaints.ProductionFacility

That will give you a count by facility by date - one count for each
combination.

If you don't want the count for each combination, you will need to remove a
field (or fields) from both the select clasuse and the group by clause.



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

JohnLute said:
I have a query and it returns what's expected:

SELECT tblComplaints.DateReceived, tblComplaints.ProductionFacility
FROM tblFacilities INNER JOIN tblComplaints ON tblFacilities.txtFacilityID
=
tblComplaints.ProductionFacility
WHERE (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
((tblComplaints.ProductionFacility)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
(([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));

I'm trying to count the total number of records and so I've added:
SELECT tblComplaints.DateReceived, tblComplaints.ProductionFacility,
Count([ProductionFacility]) AS TotalComps
FROM tblFacilities INNER JOIN tblComplaints ON tblFacilities.txtFacilityID
=
tblComplaints.ProductionFacility
WHERE (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
((tblComplaints.ProductionFacility)=[Forms]![frmComplaintQueriesReports]![cbFacility]))
OR (((tblComplaints.DateReceived) Between
[Forms]![frmComplaintQueriesReports]![BeginningDate] And
[Forms]![frmComplaintQueriesReports]![EndingDate]) AND
(([Forms]![frmComplaintQueriesReports]![cbFacility]) Is Null));

This returns an error regarding [DateReceived]:
(Error 3122) You tried to execute a query that does not include the
specified expression as part of an aggregate function or grouping.

Possible cause:

You did not enter an aggregate function in the TRANSFORM statement.

I can't make heads or tails of this. What am I doing wrong?

Thanks for your help!
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

math functions in a query 1
Help with: ...query that does not include specified expression ....as part of an aggregate function. 3
Query does not include specified expression 6
Does not include the specified expression as part of an aggregatefunction 2
You tried to execute a query that does not include the specified expression <name> as part of an agg 2
RE: You tried to execute a query that does not include the specified e 0
Combining two fileds of information in the legend of a bar chart. 4

Top