CODEWITHSUNDEEP
sqlms-accessms-access-2007
thorntonehc
thorntonehc

Reputation: 1

Microsoft Access 2007 SQL Design Query Assistance Requested

I am receiving this error in MS Access:

The SELECT Statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

The compiler is highlighting the O in O/H from the second line below, but I don't think that is the cause of the error. If you see any additional errors, please let me know and provide the corrected code. I am appreciative of all help.

SELECT Inventory.Item_No, Inventory.NDC, Pharmacy.NDC_Desc,        
ROUND(Inventory.OH_Qty_EA/Pharmacy.McK_PackQty,0) AS ‘McKesson O/H Qty’, SUM((Purchases.SUOM_Orders/COUNT(Purchases.SUOM_Orders)) * Pharmacy.Safecor_PackQty*1.5/Pharmacy.McK_PackQty) AS 'Avg Par (in McKesson SUOM)', 
SUM((Purchases.SUOM_Orders/COUNT(Purchases.SUOM_Orders)) * Pharmacy.Safecor_PackQty*1.5/Pharmacy.McK_PackQty) – SUM(Inventory.OH_Qty_EA) AS 'Avg Qty to Order (in McKesson SUOM)'
FROM (Inventory
    LEFT JOIN Pharmacy ON Inventory.Item_No=Pharmacy.Item_No)
    LEFT JOIN Purchases ON Pharmacy.Item_No=Purchases.Item_No
WHERE Purchases.Order_Date>=DATE()-120
ORDER BY Inventory.Item_No ASC;

thorntonehc: I am getting the error Cannot have aggregate function in expression. It's not liking the SUM divided by Count.

Upvotes: 0

Views: 47

Answers (0)

Related Questions