Aggregat Function Error

[LadyDi]

That works perfectly!! Thank you so much for you assistance and your patience.

 

[Brianwarnock]

Happy to of been of some use.

Brian

 

[Xx_TownDawg_xX]

This one is similar to the stated problem -- I just don't see what is wrong yet?

New query called rptDataEntry without filter: (works fine)

SELECT qryDataEntry.Defect_ID, qryDataEntry.DefDesc, qryDataEntry.AuditorType, Sum(qryDataEntry.QtyRejected) AS SumOfQtyRejected
FROM qryDataEntry
GROUP BY qryDataEntry.Defect_ID, qryDataEntry.DefDesc, qryDataEntry.AuditorType
ORDER BY Sum(qryDataEntry.QtyRejected) DESC;

New query called rptDataEntry with filter: (doesn't work)

SELECT qryDataEntry.Defect_ID, qryDataEntry.DefDesc, qryDataEntry.AuditorType, Sum(qryDataEntry.QtyRejected) AS SumOfQtyRejected
FROM qryDataEntry
GROUP BY qryDataEntry.Defect_ID, qryDataEntry.DefDesc, qryDataEntry.AuditorType
HAVING (((qryDataEntry.AuditorType)="Int"))
ORDER BY Sum(qryDataEntry.QtyRejected) DESC;

I get "You tried to execute a query that does not include the specified expression as part of the aggregate function."

Please note that AuditorType is defined as an expression in qryDataEntry.

AuditorType: IIf([tblAuditors]![AuditorDept]<>[tblSeries]![Department_ID] And [tblAuditors]![Auditor_ID]<>"OP","QA",[tblAuditors]![AuditorType])

In my application, the original rules were:

IF production supervisor (or team leader) performs an audit
  THEN “QC Audit” 
  ENDIF

However, after I got done creating my dBase application the rules changed, and I was faced with trying to make the project “smarter.” The new rules are:

IF production supervisor (or team leader) performs an audit in their own department
  THEN “QC Audit” 
  ELSE “QA Audit”
  ENDIF

The frmDataEntry uses qryDataEntry to provide this logic:

AuditorType: IIf([tblAuditors]![AuditorDept]<>[tblSeries]![Department_ID] And [tblAuditors]![Auditor_ID]<>"OP","QA",[tblAuditors]![AuditorType])

I have many other queries that are subsets of qryDataEntry.

 

[pbaldy]

Try

SELECT qryDataEntry.Defect_ID, qryDataEntry.DefDesc, qryDataEntry.AuditorType, Sum(qryDataEntry.QtyRejected) AS SumOfQtyRejected
FROM qryDataEntry
WHERE (((qryDataEntry.AuditorType)="Int"))
GROUP BY qryDataEntry.Defect_ID, qryDataEntry.DefDesc, qryDataEntry.AuditorType
ORDER BY Sum(qryDataEntry.QtyRejected) DESC;

 

[Xx_TownDawg_xX]

As usual, you are amazing. Thank you!

* clicks on pbaldy's reputation counter *

 

[pbaldy]

Always happy to help my friends down south!

I made it to your neck of the woods a few years ago. We spent a week in Little Rock, but managed a side trip to Memphis. I remember watching some good bands on Beale Street, I think it was? Way too humid there for my tastes though.

 

[Xx_TownDawg_xX]

.. remember watching some good bands on Beale Street .. too humid there for my tastes though.

It's the south. It gets hot. All good southerners know that Spring and Fall are for outdoor activities.

Just because they have the "Memphis in May" celebration when it's hot doesn't mean it's the best time to visit.