Access SQL query inner join & sum with null

1
votes

Tables: Items, Purchases, QuoteLines.

Items has fields ItemID, ItemName, IsActive
Purchases has fields ItemID, QtyPurchased
QuoteLines has fields ItemID, NegQty, IsSold

The goal is to select all ItemID's where IsActive=yes then sum all QtyPurchased and subtract only the NeqQty where IsSold=yes

I have tried:

SELECT Items.ItemID, Items.ItemName, IIF(NegQTY=NULL, SUM(QtyPurchased), SUM(NegQty+PurchasedQty)) AS Inv_Qty
FROM Purchases, Items, QuoteLines
WHERE Purchases.ItemID=Items.ItemID AND Items.ItemID=QuoteLines.ItemID
GROUP BY Items.ItemID, Items.ItemName;

This results in "your query does not include the specified expression IIF(NegQTY=NULL, SUM(QtyPurchased), SUM(NegQty+PurchasedQty))"

I also tried an inner join between tblItems and tblPurchases to get the PurchasedQty then a second inner join between tblItems and tblQuoteLines to get only the SoldQty, then a left inner join between the PurchasedQty and SoldQty to get InventoryQty, this however gave the right value for items that existed in both PurchasedQty and SoldQty, but for items that only existed in PurchasedQty it returned NULL.

1
sqlinner-joinms-access-2013inventory

1 Answers

0
votes

Consider using saved queries to calculate the total units purchased and the total units sold, then use those saved queries in the query that calculates the current inventory.

Total Units Purchased

Create a saved query named [TotalPurchasedByItemID]

Purchased.png

SELECT 
    Items.ItemID, 
    Sum(Purchases.QtyPurchased) AS SumOfQtyPurchased
FROM 
    Items 
    INNER JOIN 
    Purchases 
        ON Items.ItemID = Purchases.ItemID
WHERE (((Items.IsActive)=True))
GROUP BY Items.ItemID;

Total Units Sold

Create a saved query named [TotalSoldByItemID]

Sold.png

SELECT 
    Items.ItemID, 
    Sum(QuoteLines.NegQty) AS SumOfNegQty
FROM 
    Items 
    INNER JOIN 
    QuoteLines 
        ON Items.ItemID = QuoteLines.ItemID
WHERE (((Items.IsActive)=True) AND ((QuoteLines.IsSold)=True))
GROUP BY Items.ItemID;

Current Inventory

CurrentInventory

SELECT 
    Items.ItemID, 
    Items.ItemName, 
    Nz([SumOfQtyPurchased],0)-Nz([SumOfNegQty],0) AS Inv_Qty
FROM 
    (
        Items 
        LEFT JOIN 
        TotalPurchasedByItemID 
            ON Items.ItemID = TotalPurchasedByItemID.ItemID
    ) 
    LEFT JOIN 
    TotalSoldByItemID 
        ON Items.ItemID = TotalSoldByItemID.ItemID
WHERE (((Items.IsActive)=True));