Cannot have aggregate function in WHERE clause ...

Question

I normally would do a lot of searching before posting this question, but I am in a bit of a time constraint to resolve this issue tonight.

I have a query with a series of field from several joined tables.  The whole point is to calculate a grand total value for several numeric fields.  So I have sum() ...

The curve ball is that I also have to perform a mathematical verification to ensure that a given equation based on several of the fields is <0.02  otherwise they should not be included.  When I add in the Expression or Where Total field I get this error.  How can I fix it?

Here is the SQL as it stands now (which is generating the error)

SELECT [FinRes].[902-01 PRIX DE VENTE] AS [Prix de vente], [FinRes].[902-02 BAC] AS Coutant, Nz([Chg Coutant],0) AS [Coutant des chgs], Nz([Chg Facture],0) AS [Facture des chgs], Nz([Variance_du_projet],0.01) AS [Variance du projet], [904-Livrables Global].ETC_du_projet AS [ETC du projet], Sum([Deliverables].[902-07_COÛT $ BAC]) AS [Total BAC], Sum(Nz([902-16_COÛT $ AC],0)) AS [Total AC], Sum([Deliverables].[902-17_ETC]) AS [Total ETC]

FROM (([Projects] LEFT JOIN [DeliverablesGlobal] ON [Projects].[ProjNo] = [DeliverablesGlobal].Numéro) LEFT JOIN [FinRes] ON [Projects].[ProjNo] = [FinRes].[ProjNo]) LEFT JOIN ([Deliverables] LEFT JOIN [qry_Changements_Totaux par projet] ON [Deliverables].[ProjNo] = [qry_Changements_Totaux par projet].[118-01_Numéro de Projet]) ON [Projects].[ProjNo] = [Deliverables].[ProjNo]

WHERE (((((([902-01 PRIX DE VENTE]+[Chg Facture])-(Sum(Nz([902-17_ETC],0))+Sum(Nz([902-16_COÛT $ AC],0))))/([902-01 PRIX DE VENTE]+[Chg Facture]))-((([902-01 PRIX DE VENTE]+[Chg Facture])-([902-02 BAC]+[Chg Coutant]))/([902-01 PRIX DE VENTE]+[Chg Facture])))<-0.02) AND (([Projects].[910 STATU])<>'Annulé') AND ((Year([900-05_DEBUT DU PROJET]))='2010') AND ((Mid([Projects].[ProjNo],3,1)) In ("E","M","N","U")))

GROUP BY [FinRes].[902-01 PRIX DE VENTE], [FinRes].[902-02 BAC], Nz([Chg Coutant],0), Nz([Chg Facture],0), Nz([Variance_du_projet],0.01), [Deliverables Global].ETC_du_projet;

Please excuse the mess in field naming (special chars, spaces, ect.)...  I have been given a mess of a db to try and cleanup!

Thank you,

QB

Answer 1

What was the expression you omitted and whose post were you trying?

---

Build a little, test a little.

Answer 2

I was working with Marshall's, but either way you are both telling me to do the same thing and switch to using the HAVING clause.

Answer 3

I continued to fiddle.  Added a few more Nz() around all my fields as apparently the data has holes!  Now if I remove the criteria, it works and return the value properly.  If I add the criteria to the query field then it spits out the error!?

Answer 4

Now if I take my whole expression minus the <-0.02 criteria, throw it as a subquery and apply the criteria to it it works, but this IMHO is messy    or is this proper?

Answer 5

You're assuming that we can see your query - "throw it in a subquery"???

Please post your SQL and a verbal description of the logic behind it: what are you trying to accomplish. Not knowing anything about your data structure or your data content makes it hard to guess.

---

John W. Vinson/MVP