Null Problem?

[Rjc8513]

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];

 

[PHV]

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

 

[Rjc8513]

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

 

[PHV]

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

 

[Rjc8513]

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.