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

The conditions in the WHERE clause that use an aggregate function have to be moved to the HAVING clause.  If a condition does not use an aggregate function, it should stay in the WHERE clause.

WHERE 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

HAVING (([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

I probably got lost in the parenthesis so double check them.

Answer 2

Here is a stupid question, but here goes:

What is an aggregate function?  It is a term I am unfamiliar with.

I will try your suggestion immediately and post back shortly.

Thank you for the help with this!

Answer 3

UNTESTED          UNTESTED

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  (([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

HAVING ((((([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);

---

Build a little, test a little.

Answer 4

Now I am getting the following message

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

Answer 5

Never mind.  I actually took the time to check the help file (there's a concept).