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.
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];
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.