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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Null Problem?

Status
Not open for further replies.

Rjc8513

Technical User
Feb 12, 2001
140
US
I have a query with two other queries ([ACTL DATA] and [SPLY DATA]) as it's source. There is one entity (Site A) present in [ACTL DATA] but not present in [SPLY DATA]. Sites B-D are present in both [ACTL DATA] and [SPLY DATA]. When i run the query, Sites B-D display with no problem. No matter what I try, I cannot get Site A to display. I want Site A to display zeroes for SPLY data. Any suggestions?

Here is the code (parsed to make it easier to read):

SELECT [ACTL DATA].[FINANCE NO], [ACTL DATA].[OFFICE],
[ACTL DATA].[YRPPWK], [ACTL DATA].[FUNCTION],
Sum([ACTL DATA].[WKHRS]), Sum([ACTL DATA].[OTHRS]),
Sum([ACTL DATA].[POTHRS]),

IIf((Sum([ACTL DATA].[OTHRS])+Sum([ACTL DATA].[POTHRS]))=0
Or Sum([ACTL DATA].[WKHRS])=0,0,
(Sum([ACTL DATA].[OTHRS])+Sum([ACTL DATA].[POTHRS]))/
Sum([ACTL DATA].[WKHRS])*100),

IIf(Sum([ACTL DATA].[POTHRS])=0 Or Sum([ACTL DATA].[WKHRS])=0,
0,(Sum([ACTL DATA].[POTHRS])/Sum([ACTL DATA].[WKHRS]))*100),
Sum([SPLY DATA].[WKHRS]), Sum([SPLY DATA].[OTHRS]),
Sum([SPLY DATA].[POTHRS]),

IIf((Sum([SPLY DATA].[OTHRS])+Sum([SPLY DATA].[POTHRS]))=0 Or
Sum([SPLY DATA].[WKHRS])=0,0,
((Sum([SPLY DATA].[OTHRS])+Sum([SPLY DATA].[POTHRS]))/
Sum([SPLY DATA].[WKHRS]))*100),

IIf(Sum([SPLY DATA].[POTHRS])=0 Or Sum([SPLY DATA].[WKHRS])=0,0,(Sum([SPLY DATA].[POTHRS])/Sum([SPLY DATA].[WKHRS]))*100)

FROM [ACTL DATA] INNER JOIN [SPLY DATA]
ON ([ACTL DATA].[FUNCTION] = [SPLY DATA].[FUNCTION])
AND ([ACTL DATA].[FINANCE NO] = [SPLY DATA].[FINANCE NO])

GROUP BY [ACTL DATA].[FINANCE NO], [ACTL DATA].[OFFICE], [ACTL DATA].[YRPPWK],
[ACTL DATA].[FUNCTION], [ACTL DATA].[FUNCTION]

HAVING ((([ACTL DATA].FUNCTION)<>'OTHER'))

ORDER BY [ACTL DATA].[FINANCE NO];
 
As a starting point, replace this:
FROM [ACTL DATA] INNER JOIN [SPLY DATA]
with this:
FROM [ACTL DATA] LEFT JOIN [SPLY DATA]

You may have to take a look at the Nz function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
PHV, thanks for the response.

I changed the join to a Left Join as you suggested but now I get a data type mismatch error message. I checked and the joined fields are identical (they are all text).
 
And what about the Nz function for each numeric field of [SPLY DATA] in the SELECT clause ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
You're a genius, PHV!

Changing:

SELECT [ACTL DATA].[FINANCE NO], [ACTL DATA].[OFFICE],
[ACTL DATA].[YRPPWK], [ACTL DATA].[FUNCTION],
Sum([ACTL DATA].[WKHRS]), Sum([ACTL DATA].[OTHRS]),
Sum([ACTL DATA].[POTHRS])...

FROM [ACTL DATA] INNER JOIN [SPLY DATA]
ON ([ACTL DATA].[FUNCTION] = [SPLY DATA].[FUNCTION])
AND ([ACTL DATA].[FINANCE NO] = [SPLY DATA].[FINANCE NO])...


To:

SELECT [ACTL DATA].[FINANCE NO], [ACTL DATA].[OFFICE],
[ACTL DATA].[YRPPWK], [ACTL DATA].[FUNCTION],
Nz(Sum([ACTL DATA].[WKHRS]),0), Nz(Sum([ACTL DATA].[OTHRS]),0),
Nz(Sum([ACTL DATA].[POTHRS]),0)...

FROM [ACTL DATA] LEFT JOIN [SPLY DATA]
ON ([ACTL DATA].[FUNCTION] = [SPLY DATA].[FUNCTION])
AND ([ACTL DATA].[FINANCE NO] = [SPLY DATA].[FINANCE NO])...

did it.

Thanks for the help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top