Missing query data when using Sum

My database in Microsoft Access looks like this:

Every Client can have many assistants.
Every Assistant may have one client or no clients at all.

Assistant have a Nice field which is Boolean, indicating whether the Assistant is nice.

I need a query where I can get all of the clients, together with a boolean value indicating whether they have at least one nice assistant.

Currently this is the query I have:

This query is working as you can see: (apperantly 0 is false and -1 is true)

But here is the problem:

If there is a Client with no Assistants at all, it will not show up in the query.

I am wondering if there is a way to add all of the Clients with no Assistant to the query and their MinOfAS-Nice column will be 0. I will also accept any other creative way for example creating another query – but in the end I’m going to need a one query with all of the Clients data.

I need this data for a Report I’m going to create in Access.

Thanks in advance!

GitHub repo: https://github.com/orihpt/MissingRecordsInQueryAccessIssue

For your convinience here is the query as SQL query:

SELECT Client.[CL-ID], Client.[CL-Name], Min(Assistant.[AS-Nice]) AS [MinOfAS-Nice]
FROM Client INNER JOIN Assistant ON Client.[CL-ID] = Assistant.[AS-Client]
GROUP BY Client.[CL-ID], Client.[CL-Name]
ORDER BY Client.[CL-ID];

Answer

Use a Left Join:

SELECT Client.[CL-ID], Client.[CL-Name], Min(Nz(Assistant.[AS-Nice], 0)) AS [MinOfAS-Nice]
FROM Client LEFT JOIN Assistant ON Client.[CL-ID] = Assistant.[AS-Client]
GROUP BY Client.[CL-ID], Client.[CL-Name]
ORDER BY Client.[CL-ID];

Also: the Nz function is a default operator, means that on records without any linked records on the another table you will get 0 instead of Null.

But here is the problem:

Advertisement

Answer