Summation fails if Nz used

[Access Greenhorn]

If the Nz function is not used in the code below the summation of the field
QtySent is succesful. If the Nz function is applied to the QtySent field the
summation is still successful. But as soon as you apply Nz to the
LossedorTossed field the summation fails. Instead there is a row for each
QtySent entry and the batchcode (PK) and QtyMade and LossedorTossed field
entries are repeated. For example, 5 of item aa were made, 3 were sent out in
one package, 1 was sent out in another package, so the math/ row of data
should be aa, 5-4=11 but instead I get aa, 5-3=2 and aa, 5-1=4.

Sure, I can solve this by defaulting my LossedorTossed field to zero and
only use the Nz function on QtySent but I would like to know why this code is
failing.

By the way when I write out Nz([field],0) I get the message " You tried to
execute a query that does not include the specified expression
'tblProd1A.QtyMade-nz([QtySent],0)-nz([LossedorTossed],0)' as part of an
aggregate function."

Any idea?

Thanks,
Access Greenhorn

SELECT tblInventoryA.Batchcode, tblProd1A.QtyMade, Sum(tblKitsSent.QtySent)
AS SumOfQtySent, tblInventoryA.LossedorTossed,
[QtyMade]-nz([QtySent])-nz([LossedorTossed]) AS OnHand
FROM tblProd1A LEFT JOIN (tblInventoryA LEFT JOIN tblKitsSent ON
tblInventoryA.Batchcode=tblKitsSent.Batchcode) ON
tblProd1A.Batchcode=tblInventoryA.Batchcode
GROUP BY tblInventoryA.Batchcode, tblProd1A.QtyMade,
tblInventoryA.LossedorTossed, [QtyMade]-nz([QtySent])-nz([LossedorTossed]);

 

[Jeff L]

A query with a Group By groups outputted rows that are exactly the
same. You said that you are getting two rows, so there must be
something about those rows that are different. You may need to
accomplish your task using more than 1 query.

 

[Access Greenhorn]

There is something diff, 3 of item aa were sent in package 1, 1 of item aa
was sent in package 2. That is why this field is being summed to begin with.

Keep in mind, the group by summation works if the Nz function is not used or
if the Nz function is used with the QtySent field (the field that is being
summed). It fails when the Nz function is applied to the LossedorTossed
category.