Nz doesn't work with LEFT JOIN

[JeffCaz]

Hello. I have spent many hours on this simple query trying to get it to work. Can someone out there help me out, please? It is returning the wrong amount for Nz(Sum(i.quantpacked),0) AS qtyPckd, but if I do just that part in another query, it works just fine. (see the second query listed.)

Listing 1 - this DOES NOT work - it returns 20 instead of 10
for qtyPckd - WHY???

SELECT c.companyid, pl.ponumber, pl.linenum, pl.description, pl.quantity,
Max(pl.total) AS qtyRcvd,
Nz(Sum(i.quantpacked),0) AS qtyPckd,
Max(pl.total)-Nz(Sum(i.quantpacked),0) AS qtyOnHand
FROM customers AS c
INNER JOIN
((poline AS pl LEFT JOIN inventory AS i
ON pl.ponumber = i.po AND pl.linenum = i.linenum)
INNER JOIN PurchaseOrder AS po
ON pl.ponumber = po.PONumber
)
ON c.companyid = po.CompanyId
GROUP BY c.companyid, pl.ponumber, pl.linenum, pl.description, pl.quantity
HAVING pl.ponumber='100-00-273-932';

Listing 2 - this DOES work:

SELECT i.po, i.linenum, Nz(SUM(i.quantpacked),0) AS qtyPckd
FROM inventory AS i
WHERE i.po='100-00-273-932'
GROUP BY i.po, i.linenum
ORDER BY i.po, i.linenum;


The inventory table has five rows for this PO, each with a quantpacked of 2. Why won't this query work when I put it into the more complicated one above?

 

[hneal98]

try going from this: Nz(Sum(i.quantpacked),0) to this Sum(Nz(i.quantpacked,0))

See if that makes a difference.

 

[PHV]

Have you tried to replace this:
Nz(Sum(i.quantpacked),0)
By this ?
Sum(Nz(i.quantpacked,0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JeffCaz]

I appreciate the quick response and the two response are the same, so I'll give it a try. I can't let you know if it works until tomorrow... if it doesn't I'll be back. Thanks again!