CODEWITHSUNDEEP
sqldatabasems-accessrelationship
אורי orihpt
אורי orihpt

Reputation: 2634

Missing query data when using Sum

My database in Microsoft Access looks like this:

Client: CL-ID, CL-Name | Assistant: AS-ID, AS-Name, AS-Client(foregin key), AS-Nice

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)

working!

But here is the problem:

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

Every Client have at least one Assistant, except Shoshi.

Shoshi is not 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];

Upvotes: 1

Views: 81

Answers (1)

Gustav
Gustav

Reputation: 55831

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.

Upvotes: 1

Related Questions