Nz Function Not Working...

[Rjc8513]

Trying to return Unit No. 364124 with zeroes for quantity and amount but it will not show up in query results.

Any suggestions? Thanks.

SELECT [tbl OFFICE DATA].[UNIT NO], [tbl ACTL EXPENSE DATA].ITEM_NO, Sum(Nz([QUANTITY],0)) AS [ACTL QTY], Sum(Nz([EXT_PRICE],0)) AS [ACTL AMT]
FROM [tbl ACTL EXPENSE DATA] INNER JOIN [tbl OFFICE DATA] ON [tbl ACTL EXPENSE DATA].UNIT_NO = [tbl OFFICE DATA].[UNIT NO]
GROUP BY [tbl OFFICE DATA].[UNIT NO], [tbl ACTL EXPENSE DATA].ITEM_NO
HAVING ((([tbl OFFICE DATA].[UNIT NO])="360320" Or ([tbl OFFICE DATA].[UNIT NO])="361393" Or ([tbl OFFICE DATA].[UNIT NO])="361420" Or ([tbl OFFICE DATA].[UNIT NO])="362681" Or ([tbl OFFICE DATA].[UNIT NO])="364124" Or ([tbl OFFICE DATA].[UNIT NO])="368640") AND (([tbl ACTL EXPENSE DATA].ITEM_NO)="30308"));

 

[lespaul]

are you sure that unit 364124 is in both

tbl ACTL EXPENSE DATA
and
tbl OFFICE DATA

if it's not in both tables you'll need to use a LEFT join instead of an INNER JOIN.

additionally you can remove the HAVING clause and put it in a WHERE clause since there are no aggregates in the criteria and you can use the IN clause:

SELECT O.[UNIT NO], E.ITEM_NO, Sum(Nz([QUANTITY],0)) AS [ACTL QTY], Sum(Nz([EXT_PRICE],0)) AS [ACTL AMT]
FROM [tbl ACTL EXPENSE DATA] E
INNER JOIN [tbl OFFICE DATA] O ON E.UNIT_NO = O.[UNIT NO]
WHERE O.[UNIT NO]) IN ("360320", "361393", "361420", "362681", "364124", "368640") AND E].ITEM_NO ="30308"
GROUP BY [tbl OFFICE DATA].[UNIT NO], [tbl ACTL EXPENSE DATA].ITEM_NO
;

Leslie

Have you met Hardy Heron?

 

[Rjc8513]

Thanks for the response Leslie. But, I still must have something wrong. Following is my altered code but it is still not returning Unit 364124, which exists in the [OFFICE DATA] table, not in the [ACTL EXPENSE DATA] table.

SELECT [tbl OFFICE DATA].[UNIT NO], [tbl ACTL EXPENSE DATA].ITEM_NO, Sum(Nz([QUANTITY],0)) AS [ACTL QTY], Sum(Nz([EXT_PRICE],0)) AS [ACTL AMT]

FROM [tbl OFFICE DATA] LEFT JOIN [tbl ACTL EXPENSE DATA] ON [tbl OFFICE DATA].[UNIT NO]=[tbl ACTL EXPENSE DATA].UNIT_NO

WHERE [tbl OFFICE DATA].[UNIT NO] In ("360320","361393","361420","362681","364124","368640") And (([tbl ACTL EXPENSE DATA].ITEM_NO)="30308")

GROUP BY [tbl OFFICE DATA].[UNIT NO], [tbl ACTL EXPENSE DATA].ITEM_NO;

 

[Golom]

This

WHERE [tbl OFFICE DATA].[UNIT NO] In ("360320","361393","361420","362681","364124","368640") And  
[red](([tbl ACTL EXPENSE DATA].ITEM_NO)="30308")[/red]

has the effect of converting your LEFT JOIN into an INNER JOIN.

The record where [tbl OFFICE DATA].[UNIT NO] has the value "364124" will also have all the fields in [tbl ACTL EXPENSE DATA] = NULL because that [UNIT_NO] doesn't exist in that table.

By including the clause in [red]red[/red] above you have excluded that record from the query.

 

[PHV]

You may try this:

SELECT O.[UNIT NO], E.ITEM_NO, Sum(Nz([QUANTITY],0)) AS [ACTL QTY], Sum(Nz([EXT_PRICE],0)) AS [ACTL AMT]
FROM [tbl OFFICE DATA] O LEFT JOIN (
SELECT * FROM [tbl ACTL EXPENSE DATA] WHERE ITEM_NO="30308"
) E ON O.[UNIT NO]=E.UNIT_NO
WHERE O.[UNIT NO] In ("360320","361393","361420","362681","364124","368640")
GROUP BY O.[UNIT NO], E.ITEM_NO

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886