Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Nz Function Not Returning All Values...

Status
Not open for further replies.

Rjc8513

Technical User
Feb 12, 2001
140
US
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.
 
Seems like LDC isn't a field of [tbl OFFICE DATA] ...
 
No, it is not. Is that necessary? If so, can you suggest a work-around?

Thanks.
 
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
 
Thanks PHV. Not sure what you mean by A. and D.
 
They are alias.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.

 
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.

 
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

 
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.
 
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.
 
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

 
Do you really need the YRWK column ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top