CODEWITHSUNDEEP
sqlms-accesscountleft-joininner-join
user2435645
user2435645

Reputation: 3

SQL - Display Count of records even if 0

I am trying to create an SQL query in MS Access that will show how many appointments an employee will have in the current month, even if it is 0. It is very similar to this question, but i can't get it to work with a WHERE clause.

I have 3 tables:

tblEmployees
-employeeID PK
-FirstName ETC

tblEngineersAppts1
-ApptID PK
-EmployeeID*

tblEngineersAppts2
-DiaryID PK
-ApptDate
-ApptID*

I want to show all employees, a COUNT of all appointments (DiaryID) in tblEngineersAppts2 even if there are none where ApptDate is the current month. This is my query, it only shows employees that have an appointment in the current month, it doesn't show those who have none.

SELECT tblEmployees.EmployeeID, Count(tblEngineersAppts2.DiaryID) AS CountOfDiaryID
FROM (tblEmployees
LEFT JOIN tblEngineersAppts1 ON tblEmployees.EmployeeID = tblEngineersAppts1.EmployeeID)
LEFT JOIN tblEngineersAppts2 ON tblEngineersAppts1.ApptID = tblEngineersAppts2.ApptID
WHERE (((Format$([ApptDate],'MM/YY'))='03/17'))
GROUP BY tblEmployees.EmployeeID;

Thanks

Upvotes: 0

Views: 187

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

The problem is when you put the WHERE condition you make the LEFT JOIN an INNER JOIN

WHERE (((Format$([ApptDate],'MM/YY'))='03/17'))

So Include the ApptDate constraint in the ON condition.

SELECT tblEmployees.EmployeeID, Count(tblEngineersAppts2.DiaryID) AS CountOfDiaryID
FROM (tblEmployees
LEFT JOIN tblEngineersAppts1 ON tblEmployees.EmployeeID = tblEngineersAppts1.EmployeeID)
LEFT JOIN tblEngineersAppts2 
  ON (   tblEngineersAppts1.ApptID = tblEngineersAppts2.ApptID
     AND Format$([ApptDate],'MM/YY')='03/17'
     )
GROUP BY tblEmployees.EmployeeID;

Upvotes: 2

Arion
Arion

Reputation: 31239

I think you could do something like this:

SELECT
    tblEmployees.EmployeeID,
    (
        SELECT Count(tblEngineersAppts2.DiaryID)
        FROM tblEngineersAppts1
        JOIN tblEngineersAppts2 ON tblEngineersAppts1.ApptID = tblEngineersAppts2.ApptID
        WHERE tblEmployees.EmployeeID = tblEngineersAppts1.EmployeeID
        AND (((Format$([ApptDate],'MM/YY'))='03/17'))
    ) AS CountOfDiaryID
FROM
    tblEmployees

Upvotes: 1

Related Questions