MS Access SQL Query Not Exist

Question

I have been stuck on trying to figure how to return data that is in one table but not the other. I thought an outter join would work, but it seems that Access does not allow that.

My SQL is returning results if a record exists in the MonthlyTargets_0_SPARTN_qry but if there is not record then no data is being returned. I would like to display a 0 if there are not records.

My sql is:

SELECT REF_TestCategory_tbl.CategoryID
    ,MonthlyTargets_0_SPARTN_qry.[Supervisor Id] AS TestOfficerID
    ,Count(MonthlyTargets_0_SPARTN_qry.[Sheet ID]) AS Actuals
    ,MonthlyTargets_0_SPARTN_qry.ComplianceMonth
FROM MonthlyTargets_0_SPARTN_qry
INNER JOIN (
    REF_TestCategory_tbl INNER JOIN REF_TestCatalog_tbl ON REF_TestCategory_tbl.CategoryID = REF_TestCatalog_tbl.TestCategory
    ) ON MonthlyTargets_0_SPARTN_qry.[Test Number] = REF_TestCatalog_tbl.TestID
GROUP BY REF_TestCategory_tbl.CategoryID
    ,MonthlyTargets_0_SPARTN_qry.[Supervisor Id]
    ,MonthlyTargets_0_SPARTN_qry.ComplianceMonth
ORDER BY REF_TestCategory_tbl.CategoryID;

Which returns:

CategoryID  TestOfficerID   Actuals     ComplianceMonth
    1           3062            26      1/1/2020
    1           3062            6       2/1/2020
    2           3062            2       1/1/2020
    3           3062            2       1/1/2020
    3           3062            1       2/1/2020

if there are no records for feb, I need it to reurn 0 in Actuals Thank you

Does this answer your question? [How do I write a full outer join query in access](https://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access) — Igor, Feb 12 '20 at 17:19
↑ You can do that and then check if the table's PK column has a NULL. — Igor, Feb 12 '20 at 17:19
I am not sure how to do that because of my joins and group by. MonthlyTargets_0_SPARTN_qry INNER JOIN (REF_TestCategory_tbl INNER JOIN REF_TestCatalog_tbl ON REF_TestCategory_tbl.CategoryID = REF_TestCatalog_tbl.TestCategory) ON MonthlyTargets_0_SPARTN_qry.[Test Number] = REF_TestCatalog_tbl.TestID GROUP BY REF_TestCategory_tbl.CategoryID, MonthlyTargets_0_SPARTN_qry.[Supervisor Id], MonthlyTargets_0_SPARTN_qry.ComplianceMonth ORDER BY REF_TestCategory_tbl.CategoryID; — Tom, Feb 12 '20 at 18:27

Heinrich · Answer 1 · 2020-02-13T20:35:08.207

0

If your 'ComplianceMonth' Values consistently exists regardless of your adjacent data(Meaning if the adjacent data returned for your ComplianceMonth is NULL) then you could do something like this.

    SELECT REF_TestCategory_tbl.CategoryID, 
       MonthlyTargets_0_SPARTN_qry.[Supervisor Id] AS TestOfficerID, 
       coalesce(Count(MonthlyTargets_0_SPARTN_qry.[Sheet ID]),0) AS Actuals,
       MonthlyTargets_0_SPARTN_qry.ComplianceMonth
FROM            dbo.MonthlyTargets_0_SPARTN_qry RIGHT OUTER JOIN
                         dbo.REF_TestCategory_tbl RIGHT OUTER JOIN
                         dbo.REF_TestCatalog_tbl ON REF_TestCategory_tbl.CategoryID = REF_TestCatalog_tbl.TestCategory ON MonthlyTargets_0_SPARTN_qry.[Test Number] = REF_TestCatalog_tbl.TestID
GROUP BY REF_TestCategory_tbl.CategoryID, MonthlyTargets_0_SPARTN_qry.[Supervisor Id], MonthlyTargets_0_SPARTN_qry.ComplianceMonth
ORDER BY REF_TestCategory_tbl.CategoryID

Hope this Helps.

edited Feb 13 '20 at 20:35

answered Feb 12 '20 at 19:32

Heinrich

36
6

I changed coalesce(Count(MonthlyTargets_0_SPARTN_qry.[Sheet ID]),0) AS Actuals to NZ(Count(MonthlyTargets_0_SPARTN_qry.[Sheet ID]),0) AS Actuals because Access does not have the coalasce function. The query is still not returning null values. – Tom Feb 13 '20 at 13:08
CategoryID & TestID are constant, compliance month can be null. – Tom Feb 13 '20 at 19:43
19 category's (buckets) & TestID that fill buckets. Ex: Category 1 has testid 1,2,3 Category 2 testid 4,5,6. Completed tests are in MonthlyTargets_0_SPARTN_qry. MonthlyTargets_0_SPARTN_qry has the test number, person that completed test (TestOfficerID), date test entered (ComplianceMonth). Trying to get how many of each category each test officer has entered per month. – Tom Feb 13 '20 at 19:54
I need to see the 0 count for category 2 in 02/01/2020: CatID TestOfficerID Actuals ComplianceMonth 1 3062 2 1/1 1 3062 6 2/1 2 3062 2 1/1 2 3062 0 2/1 3 3062 2 1/1 3 3062 1 2/1 – Tom Feb 13 '20 at 19:54
Use 'Right Outer Join' Or 'Right Join' instead of Inner join. – Heinrich Feb 13 '20 at 20:21
If there are no Records for 'Feb', 'Feb' must still exist in the query for the count to be 0 in Feb – Heinrich Feb 13 '20 at 20:26
I Have edited the original answer to show what the right join could look like. – Heinrich Feb 13 '20 at 20:36
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/207839/discussion-between-tom-and-heinrich). – Tom Feb 14 '20 at 12:49

Arturo Azcorra · Answer 2 · 2021-09-15T13:30:15.917

MS-Access DOES allow outer joins in its SQL. You can do both a LEFT JOIN or a RIGHT JOIN.

MS-Access does not include a statement for a full-outer-join. However, if you want to do a full-outer-join you can do it with a UNION ALL of a specific LEFT JOIN and a specific RIGHT JOIN. The instructions to do a full-outer-join are the following:

You do a “LEFT JOIN” (enclosed in a Select operation) between the two input record-lists. If one of the two input record-lists has one (or more) fields that for sure cannot be Null, that will be the left input record-list. The “ON” Boolean expression is the one that you want for the Full-Outer-Join.
If the left record-list has one (or more) fields that for sure cannot be Null, you skip this step. Otherwise, you do a Cross-Join between the left record-list and a record-list having only one record with one non-Null field (it can be exactly the same Select over “T_Numbers” in the example above, highlighted in green). The Cross-Join is enclosed in a Select that exposes all the fields from the Cross-Join operation, including the field “Num” from “T_Numbers” (with another field name, if you want).
You do a “RIGHT JOIN” having the same right input record-list from point 1. Its left record-list is either the Select from point 2, or the left input record-list from point 1, as corresponds (see point 2). The “ON” expression must be exactly the same as the one of the “LEFT JOIN” from point 1.
The “RIGHT JOIN” from point 3 is enclosed in a Select that exposes all the fields from the left and right input record-lists from point 1. This Select has the “WHERE” expression “IsNull(field)”, where “field” is either the “Num” field from point 2, or the field from left input record-list that for sure cannot be Null, as corresponds (see point 2).
You do a “UNION ALL” with the Select enclosing the “RIGHT JOIN” from point one and the Select enclosing the “RIGHT JOIN” from point 4.

More information at LightningGuide.net.

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-ask). — Community, Sep 14 '21 at 23:49

MS Access SQL Query Not Exist

2 Answers2