Make table query returning no records

[Mike Hughes]

There are no closed cases in this CASELOAD and nothing is being inserted into TABLE 15. Not the CASELOAD name or the number of CLOSED CASES in this case '0'.

Is there a way to have the query insert the CASELOAD 'name' and the number of CLOSED CASES as '0'?


SELECT
[2 WORKER CASES].CASELOAD,

Count(NOLDBA_INT_CASE_STATUS.CASE_ID) AS [CLOSED CASES]
INTO [TABLE 15]

FROM [2 WORKER CASES] INNER JOIN NOLDBA_INT_CASE_STATUS ON [2 WORKER CASES].CASE = NOLDBA_INT_CASE_STATUS.CASE_ID

WHERE NOLDBA_INT_CASE_STATUS.CASE_STATUS="C"

GROUP BY [2 WORKER CASES].CASELOAD

Thanks

 

[MSAccessRookie]

Try using a "LEFT JOIN" instead of an "INNER JOIN" in the SubQuery, and change the Where to include records where NOLDBA_INT_CASE_STATUS.CASE_ID is Null, and see if that brings you closer to what you want..

 

[Mike Hughes]

No, that didn't work:

SELECT
[2 WORKER CASES].CASELOAD,
Count(NOLDBA_INT_CASE_STATUS.CASE_ID) AS [CLOSED CASES] INTO [TABLE 15]

FROM [2 WORKER CASES] LEFT JOIN NOLDBA_INT_CASE_STATUS ON [2 WORKER CASES].CASE = NOLDBA_INT_CASE_STATUS.CASE_ID

WHERE NOLDBA_INT_CASE_STATUS.CASE_STATUS="C" AND NOLDBA_INT_CASE_STATUS.CASE_ID IS NULL

GROUP BY [2 WORKER CASES].CASELOAD;