laurenh5516

New Member
Joined
Nov 20, 2017
Messages
9
I have a query that has always worked, but now does not work and I get an Overflow error. I read that it has to do with dividing by zero or null values, but when I try to remove nulls or use criteria >0 I get an error that says "Your query does not include the specified expression'qry_ALPS_P6Earned.EarnedHrs>0' as part of an aggregate function. Here is my query below:

SELECT qry_ALPS_Actuals.AreaLead, qry_ALPS_Actuals.Sub_PJN, Sum(qry_ALPS_ConstructionEst.SumofTotal) AS EstTotal, Sum(qry_ALPS_Actuals.TotalCost) AS ActualCost, qry_ALPS_P6Earned.SumOfTotalHoursDirect AS PlannedHrs, qry_ALPS_P6Earned.EarnedHrs, qry_ALPS_P6Earned.SumOfRemainingTotalDirect AS RemainingHrs, Sum([EarnedHrs]/[SumOfTotalHoursDirect]) AS PercCom, Abs(Sum([EarnedHrs]/[SumOfTotalHoursDirect])*Sum([SumofTotal])-Sum([TotalCost])) AS OverSpend

FROM tbl_MasterProjList INNER JOIN ((qry_ALPS_Actuals LEFT JOIN qry_ALPS_ConstructionEst ON (qry_ALPS_Actuals.AreaLead = qry_ALPS_ConstructionEst.AreaLead) AND (qry_ALPS_Actuals.Sub_PJN = qry_ALPS_ConstructionEst.Sub_PJN)) LEFT JOIN qry_ALPS_P6Earned ON (qry_ALPS_Actuals.Sub_PJN = qry_ALPS_P6Earned.Sub_PJN) AND (qry_ALPS_Actuals.AreaLead = qry_ALPS_P6Earned.AreaLead)) ON tbl_MasterProjList.SUB_PJN = qry_ALPS_Actuals.Sub_PJN

WHERE (((tbl_MasterProjList.Status)="Active"))

GROUP BY qry_ALPS_Actuals.AreaLead, qry_ALPS_Actuals.Sub_PJN, qry_ALPS_P6Earned.SumOfTotalHoursDirect, qry_ALPS_P6Earned.EarnedHrs, qry_ALPS_P6Earned.SumOfRemainingTotalDirect

HAVING (((qry_ALPS_P6Earned.EarnedHrs)>0))

ORDER BY qry_ALPS_Actuals.AreaLead, Abs(Sum([EarnedHrs]/[SumOfTotalHoursDirect])*Sum([SumofTotal])-Sum([TotalCost])) DESC;
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,690
Office Version
  1. 2013
Platform
  1. Windows
Since you are left joining to qry_ALPS_P6Earned it is not possible to exclude nulls (otherwise, you would not use a left join in the first place). The solution may be to avoid nulls or zeros (since you should never include nulls in a sum and you should never have zero in a divisor) but you will have to think very carefully about how you do this since you have a complex query here and your sum that you are trying to fix is only one part of it.

To debug this you might want to first look at what is in the results without grouping and see what these nulls actually are and whether you can safely filter them out without affecting the other parts of the query.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,073
Messages
5,526,651
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top