Subtracting two columns

[Djblois]

I have two fields for Inventory - one is Qty in Lot and one is Allocated in Lot. Each Product can have multiple lots, so therefore I need to sum the Qty column for the product and minus the sum of the Allocated Column. This is what I thought I needed:

Sum([QTY])-Sum([ALLOCD]) AS INV,

But that is not Summing all the Qty for that product then subtracting the sum of all the allocated for that product. What am I doing wrong?

 

[vbaInet]

Maybe just this:

Sum([QTY]-[ALLOCD]) AS INV,

 

[Djblois]

That was the first thing I tried but I just retried it to make sure and it does not work.

 

[vbaInet]

Then try creating another query, one for the sum of QTY and the other for Sum of AllocCD. They both should return the same number of records.

Create another query and join both by their ID (inner join should do)

Then subtract:

(SumOfQty - SumOfAllocCD) As INV

 

[Djblois]

Thank you for your help but that does not seem to work either. I added the two separate fields in the query to see and attached a screenshot. Sometimes it works and other times it does not.

Here is the full query:

SELECT DISTINCT WAVE3_PC_MGR.MGR1, WAVE3_PC_MGR.MGR2, WAVE3_PRODS.PC, WAVE3_PRODS.PDESC, Inventory.SumOfQTY, Inventory.SumOfALLOCD, ([SumOfQty]-[SumOfALLOCD]) AS INV, Sum(WAVE3_BLIN_INVN_LOTS.QTY_HLD) AS Held, WAVE3_PRC_CUST_V.PRICE
FROM (((WAVE3_PRC_CUST_V LEFT JOIN WAVE3_BLIN_INVN_LOTS ON WAVE3_PRC_CUST_V.PC = WAVE3_BLIN_INVN_LOTS.PC) LEFT JOIN WAVE3_PRODS ON WAVE3_PRC_CUST_V.PC = WAVE3_PRODS.PC) LEFT JOIN WAVE3_PC_MGR ON WAVE3_PRC_CUST_V.PC = WAVE3_PC_MGR.PC) LEFT JOIN Inventory ON WAVE3_PRC_CUST_V.PC = Inventory.PC
GROUP BY WAVE3_PC_MGR.MGR1, WAVE3_PC_MGR.MGR2, WAVE3_PRODS.PC, WAVE3_PRODS.PDESC, Inventory.SumOfQTY, Inventory.SumOfALLOCD, WAVE3_PRC_CUST_V.PRICE, WAVE3_PRC_CUST_V.CUST
HAVING (((WAVE3_PRC_CUST_V.CUST)="4277"));

 

[vbaInet]

Remember your Nulls?

Nz() it to 0.

 

[Djblois]

ok where do I put the NZ() to 0

What do you mean if you subtract a null value then it will come out null?

 

[vbaInet]

That's what it seems to be doing. Put it in both fields:

INV: Nz(SumOfQty, 0) - Nz(SumOfAllocCD, 0)

Or use Val():

INV: Val(SumOfQty) - Val(SumOfAllocCD)

 

[Djblois]

Thank you that worked perfectly.

 

[vbaInet]

Glad it's working.