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

I'm obviously in over my head on this one.  I don't truly understand the financial equation to even stand a chance to explain it in the first place.

Back to the drawing board tomorrow.