I get this syntax error
(Missing Operator) in query expression 'Purchases.SUOM_Orders IN (SELECT COUNT(Purchases.SUOM_Orders) FROM Purchases WHERE Purchases.Order_Date>=DATE()-120) subQryCount
There may be other errors that the compiler has not progressed to yet but if you see anything, please correct. I appreciate your help. Ignore the 'your text' parts. I tried to get my code to post in the forum, which is the reason for those parts.
SELECT
Inventory.Item_No, Inventory.NDC, Pharmacy.NDC_Desc,
ROUND(Inventory.OH_Qty_EA / Pharmacy.McK_PackQty, 0) AS ‘McKesson_OH_Qty’,
SUM(Purchases.SUOM_Orders / subQryCount.SUOM_Orders) * Pharmacy.Safecor_PackQty * 1.5 / Pharmacy.McK_PackQty AS 'Avg_Par',
SUM(Purchases.SUOM_Orders / subQryCount.SUOM_Orders) * Pharmacy.Safecor_PackQty * 1.5 - SUM(Inventory.OH_Qty_EA) / Pharmacy.McK_PackQty AS 'Avg_OrdQty'
FROM
(Inventory
LEFT JOIN
Pharmacy ON Inventory.Item_No = Pharmacy.Item_No)
LEFT JOIN
Purchases ON Pharmacy.Item_No = Purchases.Item_No
WHERE
Purchases.SUOM_Orders IN (SELECT COUNT(Purchases.SUOM_Orders)
FROM Purchases
WHERE Purchases.Order_Date >= DATE() - 120) subQryCount
GROUP BY
Purchases.Item_No ASC
HAVING
Avg_OrdQty > 0;