Annoying Access Syntax Error

[JeffK627]

The following query is giving me the error message, "You tried to execute a query that does not include the specified expression 'iMANY_Units' as part of an aggregate function." I can't figure out why, because my query DOES contain that expression. Can someone tell me what I'm missing?

SELECT ndc.Product,bus.Business_Unit,ndc.ProductDesc,ndc.NDC11,uts.State,GroupIDs.GroupName AS State_Category,uts.Quarter,uts.iMYUnits AS iMANY_Units,uts.ffsUnits AS FFS_Units,uts.mcoUnits AS MCO_Units
   FROM
(
(
(
(
((SELECT Customer AS State, NDC11, Units AS iMYUnits, 0 AS ffsUnits, 0 AS mcoUnits, Quarter FROM per_state_per_ndc11_iMY
UNION ALL
SELECT State, NDC11, 0  AS iMYUnits,Units AS ffsUnits, 0 AS mcoUnits, Quarter FROM IMS_FFS
UNION ALL
SELECT State, NDC11, 0  AS iMYUnits,0 AS ffsUnits, Units, Quarter as mcoUnits FROM IMS_MCO) AS uts)
LEFT JOIN NDC11_Dictionary AS ndc ON uts.NDC11=ndc.NDC11)
   LEFT JOIN Customer_Groups AS cg ON uts.State=cg.Customer)
   LEFT JOIN GroupIDs ON GroupIDs.GroupID=cg.Group)
   LEFT JOIN NDC11_BU_XREF AS bux ON uts.NDC11=bux.NDC11)
   LEFT JOIN Business_Units AS bus ON bux.BUID=bus.ID
GROUP BY Product,Business_Unit,ndc.NDC11,ProductDesc,State,GroupName,Quarter, iMANY_Units, ffs_Units, mco_Units;

 

[SydneyGeek]

In the Group By, replace
iMANY_Units
with
uts.iMYUnits

You can't use the alias in the Group By clause.

Denis

 

[JeffK627]

Yeah, I actually realized my mistake 5 minutes after I posted the question. Herp derp.

Thanks!