Iíve built a simple inventory tracking tool for our manufacturing department. Our products are manufactured in discrete batches, and the number of shippers produced is entered. Then shippers are sent out as customers require. I used two tables, the Batch table BatchT, and a Shipped Quantity Table tblShipQuantity (I changed my naming convention during the design of this database as you can see). I set up a continuous form via a couple queries.
SELECT Sum(tblShipQuantity.ShippedAmount) AS SumOfShippedAmount, tblShipQuantity.BatchID
GROUP BY tblShipQuantity.BatchID;
SELECT BatchT.BatchNumber, BatchT.BatchID, BatchT.NumberOfShippers, BatchT.ReadyForSaleID, Nz([qryShippedSum].[SumOfShippedAmount],0) AS SumOfShippedAmount, ProductT.ProductName, [NumberOfShippers]-[SumOfShippedAmount] AS Balance, BatchT.DispositionID, BatchT.LotNumber
FROM (BatchT LEFT JOIN qryShippedSum ON BatchT.BatchID = qryShippedSum.BatchID) INNER JOIN ProductT ON BatchT.ProductID = ProductT.ProductID
WHERE (((BatchT.ReadyForSaleID)=1) AND ((BatchT.DispositionID)=1));
As you can see qryShippedSum is included in qryInvTrackList. I wanted the continuous form to show a zero in the SumOfShippedAmount Column if there was no activity yet, so I used the Nz([blah.blah],0) thing above to display a zero, as shown in the attached image.
Everything works, but I would like to exclude batches that have a zero balance in the continuous form. I tried setting the criteria for Balance ([NumberOfShippers]-[SumOfShippedAmount] AS Balance) to > 0, but when I do that, any batches that have a zero for the Total Shipped (SumOfShippedAmount in the query) column are also excluded. Iím guessing it has something to do with nulls, versus zero, that is presently beyond my understanding. Any help is appreciated.