SubQuery MS Access SQL Code Assistance Requested

33 Views Asked by At

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;
0

There are 0 best solutions below