Access SQL IIf statement with GROUP BY (no alias utilized)

[rtsmith555]

I keep getting an error when I try to run this stating:

"Your query does not include the expression 'Iif([DEMAND Last 36 Mos]<3,0,[MAX Qty 3-Mo]-[Balance])' as part of an aggregate function."
I have included the expression under GROUP BY, however, so I'm at a loss.

I have added the expression to GROUP BY (not the alias) but the query won't recognize it. As far as I know, I'm still under the 10 field limit with GROUP BY.

Can anyone find my error?

SELECT [qry3-UNION-Most_Recent_Matl_Nums].[MATERIAL],
[NEW-ZTAC].[Material Description],
[NEW-ZTAC].[MovAvgPrice],
sum([NEW-ZTAC].[Not Allocated Q'ty]) AS [Current ATP],
sum([NEW-ZTAC]![Open PO Q'ty ZE01]+[NEW-ZTAC]![Open PO Q'ty ZN01]) AS [Due In],
sum([NEW-ZTAC]![Open SO Q'ty]) AS [Due Out],
sum([NEW-ZTAC]![Not Allocated Q'ty]+[Due In]-[Due Out]) AS [Balance],
sum([qry15-SUM of D Part 2].[Sum of 12-Mo Demand]) AS [DEMAND Last 12 Mos],
sum([qry9-SUM of Q Part 2].[SUM 12-Mo Qty]) AS [QTY Last 12 Mos],
sum([qry15-SUM of D Part 2].[SUM 36-Mo Demand]) AS [DEMAND Last 36 Mos],
sum([qry9-SUM of Q Part 2].[SUM 36-Mo Qty]) AS [QTY Last 36 Mos],
IIf([DEMAND Last 36 Mos]=0,0,[QTY Last 36 Mos]/[DEMAND Last 36 Mos]) AS [AVG Ord QTY Last 36 Mos],
sum([QTY Last 36 Mos]/36) AS [QTY Per Mo - 36 Mos],
sum(3*[QTY Per Mo - 36 Mos]) AS [3-Mo Avg],
sum([qry5-MAX Q 3-Mo Part 2].[MAX Qty 3-Mo]),
sum([qry11-MAX D 3-Mo Part 2].[MAX Demand 3-Mo]),
sum([qry7-MAX Q 4-Mo Part 2].[MAX Qty 4-Mo]),
sum([qry13-MAX D 4-Mo Part 2].[MAX Demand 4-Mo]),
[NEW-ZTAC].[Material Memo] AS [Material Text],
IIf([DEMAND Last 36 Mos]<3,0,[MAX Qty 3-Mo]-[Balance]),
IIf([DEMAND Last 36 Mos]<3,0,[MAX Qty 4-Mo]-[Balance]),
sum([MovAvgPrice]*[Order QTY 3-Mo]) AS [3-Mo Order Cost],
sum([MovAvgPrice]*[Order QTY 4-Mo]) AS [4-Mo Order Cost],
[NEW-ZTAC].[Created]

FROM [NEW-ZTAC]
RIGHT JOIN ([qry11-MAX D 3-Mo Part 2]
RIGHT JOIN ([qry5-MAX Q 3-Mo Part 2]
RIGHT JOIN ([qry13-MAX D 4-Mo Part 2]
RIGHT JOIN ([qry15-SUM of D Part 2]
RIGHT JOIN ([qry9-SUM of Q Part 2]
RIGHT JOIN ([qry7-MAX Q 4-Mo Part 2]
RIGHT JOIN [qry3-UNION-Most_Recent_Matl_Nums]
ON [qry7-MAX Q 4-Mo Part 2].MATERIAL = [qry3-UNION-Most_Recent_Matl_Nums].MATERIAL)
ON [qry9-SUM of Q Part 2].MATERIAL = [qry3-UNION-Most_Recent_Matl_Nums].MATERIAL)
ON [qry15-SUM of D Part 2].MATERIAL = [qry3-UNION-Most_Recent_Matl_Nums].MATERIAL)
ON [qry13-MAX D 4-Mo Part 2].MATERIAL = [qry3-UNION-Most_Recent_Matl_Nums].MATERIAL)
ON [qry5-MAX Q 3-Mo Part 2].MATERIAL = [qry3-UNION-Most_Recent_Matl_Nums].MATERIAL)
ON [qry11-MAX D 3-Mo Part 2].MATERIAL = [qry3-UNION-Most_Recent_Matl_Nums].MATERIAL)
ON [NEW-ZTAC].Material = [qry3-UNION-Most_Recent_Matl_Nums].MATERIAL

GROUP BY
[qry3-UNION-Most_Recent_Matl_Nums].[MATERIAL],
[NEW-ZTAC].[Material Description],
[NEW-ZTAC].[MovAvgPrice],
IIf([DEMAND Last 36 Mos]=0,0,[QTY Last 36 Mos]/[DEMAND Last 36 Mos]),
IIf([DEMAND Last 36 Mos]<3,0,[MAX Qty 3-Mo]-[Balance]),
IIf([DEMAND Last 36 Mos]<3,0,[MAX Qty 4-Mo]-[Balance]),
[NEW-ZTAC].[Created],
[NEW-ZTAC].[Material Memo];

 

[ebs17]

Access SQL?
Even the entire FROM block with the JOINs does not correspond to the syntax required by Jet-SQL. It's never going to work that way.

Your aggregations are strange and deeply nested. It's confusing anyway and probably won't work that way.

How did you write this statement?

 

[plog]

Agree with ebs. This is a Rube Goldberg set of queries. My gut tells me there's a much better way to get the information you need than this house of queries built on tables of suspect structure.

With that said, I think your error arises because you are trying to calculate a field and simutaneously use that calculated field:

SELECT
...
sum([NEW-ZTAC]![Open PO Q'ty ZE01]+[NEW-ZTAC]![Open PO Q'ty ZN01]) AS [Due In],
sum([NEW-ZTAC]![Open SO Q'ty]) AS [Due Out],
sum([NEW-ZTAC]![Not Allocated Q'ty]+[Due In]-[Due Out]) AS [Balance],
...

[Due In] and [Due Out] don't get their values until the query is run entirely. But the query can't run entirely until [Balance] is completed. But [Balance] can't be completed until [Due In] & [Due Out] are calculated. But [Due In] & [Due Out] don't get their values....etc. etc.

So (and I am not recommending this because I think you chosen the entirely wrong path already) is you would need to do the [Due In] & [Due Out] caluclations in a query then build another query using it so you can calculate [Balance].

In my experience a field called [Balance] should not be calculated by adding various fields together. Instead it should be calculated by using SUM() on just one field. Think of a bank account. You would have a tblTransactions that looks like this:

tblTransactions
trans_Date, trans_Note, trans_Amount
1/1/2023, Work Check Deposit, 1100
2/2/2023, Rent, -600
3/3/2023, Groceries, -100
4/4/2023, Tax Refund, 200

Now, when I want that balance I run this query:

SELECT SUM(trans_Amount) As Balance FROM tblTransactions

That's it, I simply sum the trans_Amount field and get my balance. I don't need to add a bunch of fields togther.

I think you have a fundamental error in your tables if you need to add so many fields together and requring so many queries to get there. I suggest you focus on fixing you tables instead of placing a band aid on this query.