Nz Function Not Returning All Values...

[Rjc8513]

I'm stumped! Trying to return all offices from the [tbl OFFICE DATA] table for Mgr. 0, for all three of the F2B, F4, and OTHER categories, even if null.

No matter what I try, I can't get the results I want. Below is SQL code.

SELECT [tbl OFFICE DATA].[FINANCE NO], [tbl OFFICE DATA].OFFICE, [tbl OFFICE DATA].MGR, [tbl ACTL DATA].YRWK, IIf([LDC]='2000' Or [LDC]='2100' Or [LDC]='2200','F2B',IIf([LDC]='4000' Or [LDC]='4100' Or [LDC]='4200','F4','OTHER')) AS FUNCTION, Nz(Sum([WK HRS]),0) AS WKHRS, Nz(Sum([OT HRS]),0) AS OTHRS

FROM [tbl ACTL DATA] RIGHT JOIN [tbl OFFICE DATA] ON [tbl ACTL DATA].[FINANCE NO] = [tbl OFFICE DATA].[FINANCE NO]

GROUP BY [tbl OFFICE DATA].[FINANCE NO], [tbl OFFICE DATA].OFFICE, [tbl OFFICE DATA].MGR, [tbl ACTL DATA].YRWK, IIf([LDC]='2000' Or [LDC]='2100' Or [LDC]='2200','F2B',IIf([LDC]='4000' Or [LDC]='4100' Or [LDC]='4200','F4','OTHER'))

HAVING ((([tbl OFFICE DATA].MGR)='0'));

Thanks.

 

[PHV]

Seems like LDC isn't a field of [tbl OFFICE DATA] ...

 

[Rjc8513]

No, it is not. Is that necessary? If so, can you suggest a work-around?

Thanks.

 

[PHV]

What about this (typed, untested) ?
SELECT O.[FINANCE NO], O.OFFICE, O.MGR, D.YRWK
, IIf(A.LDC In('2000','2100','2200'),'F2B',IIf(A.LDC In('4000','4100','4200'),'F4','OTHER')) AS FUNCTION
, Nz(Sum(D.[WK HRS]),0) AS WKHRS, Nz(Sum(D.[OT HRS]),0) AS OTHRS
FROM ([tbl OFFICE DATA] AS O
INNER JOIN (SELECT DISTINCT X.[FINANCE NO], Y.LDC FROM [tbl OFFICE DATA] AS X, [tbl ACTL DATA] AS Y) AS A ON O.[FINANCE NO] = A.[FINANCE NO])
LEFT JOIN [tbl ACTL DATA] AS D ON A.[FINANCE NO] = D.[FINANCE NO] AND A.LDC = D.LDC
WHERE O.MGR='0'
GROUP BY O.[FINANCE NO], O.OFFICE, O.MGR, D.YRWK
, IIf(A.LDC In('2000','2100','2200'),'F2B',IIf(A.LDC In('4000','4100','4200'),'F4','OTHER'))

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

 

[Rjc8513]

Thanks PHV. Not sure what you mean by A. and D.

 

[PHV]

They are alias.

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

 

[Rjc8513]

That's almost it, PHV!

Now I'm getting a Function F2B, F4, and OTHER for each OFFICE, but I'm also getting a set of three I guess Null entries for each OFFICE.

FINANCE NO OFFICE MGR YRWK FUNCTION WKHRS OTHRS
361392 Off X 0 F2B 0 0
361392 Off X 0 F4 0 0
361392 Off X 0 OTHER 0 0
361392 Off X 0 081 F2B 10 2
361392 Off X 0 081 F4 15 1
361392 Off X 0 081 OTHER 13 6

The first set of three are not needed.

 

[Rjc8513]

Almost there, PHV!

Now I'm getting a Function F2B, F4, and OTHER for each OFFICE, but I'm also getting a set of three I guess Null entries for each OFFICE.

FINANCE NO OFFICE MGR YRWK FUNCTION WKHRS OTHRS
361392 Off X 0 F2B 0 0
361392 Off X 0 F4 0 0
361392 Off X 0 OTHER 0 0
361392 Off X 0 081 F2B 10 2
361392 Off X 0 081 F4 15 1
361392 Off X 0 081 OTHER 13 6

The first set of three are not needed.

 

[lespaul]

so you don't need records where WKHRS = 0? is that the "criteria" for removing the first 3 records? Or is it where FUNCTION = 0? how do you KNOW that those records shouldn't be there?


Leslie

 

[Rjc8513]

In the above example, I should only have one set of records. If there is data for any one function, the data should be displayed. If there is not data for a function, zero should be displayed. Not both.

Thanks.

 

[Rjc8513]

PHV or Leslie,

In the above example, I should only have one set of records. If there is data for any one function, the data should be displayed. If there is not data for a function, zero should be displayed. Not both.

Any suggestions?

Thanks.

 

[lespaul]

Sorry, I'm not as good as PHV at determining what the query is doing and what it needs to do differently to get the results you need. If you want to provide some sample data from both of the tables and the expected results from that data, I may be able to figure out what the issue is.....

Leslie

 

[PHV]

Do you really need the YRWK column ?

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