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