title: MS Access / SQL Server JOIN to return all values in left table and zero if missing in right
body: <p>Apologies firstly, as I think the title possibly makes this question sound far more difficult than it needs to be.</p>
  
  <p>Essentially, I have two tables. In basic terms, it's a staff table and a "work completed" table. I'm trying to create a query that returns all the staff and the sum of the work they've done in a summary rows format.</p>
  
  <p>The two tables:</p>
  
  <p><strong>STAFF_TABLE</strong></p>
  
  <pre><code>+------------+-------------+----------------+--------------+
  | TM_StaffID | TM_TeamName | TM_TeamManager | TM_StaffName |
  +------------+-------------+----------------+--------------+
  |          1 | HA11        | David A        | Paul A       |
  |          2 | HA11        | David A        | John A       |
  |          3 | HA11        | David A        | Simon A      |
  |          4 | HA11        | David A        | Peter A      |
  +------------+-------------+----------------+--------------+
  </code></pre>
  
  <p><strong>WORK_TABLE</strong></p>
  
  <pre><code>+-------------+------------+----------+-------------+------------+---------------+-----------+
  | PS_TeamName | PS_WorkID  | PS_Staff | PS_WorkType |  PS_Date   | PS_WeekEnding | PS_Points |
  +-------------+------------+----------+-------------+------------+---------------+-----------+
  | HA11        | 2066944422 |        1 | Dev         | 02/08/2018 | 05/08/2018    |         1 |
  | HA11        | 1869145859 |        1 | * Misc.     | 02/08/2018 | 05/08/2018    |      0.75 |
  | HA11        | 2063035829 |        3 | Plan        | 01/08/2018 | 05/08/2018    |         1 |
  | HA11        | 2036440149 |        3 | * Misc.     | 02/08/2018 | 05/08/2018    |       0.5 |
  | HA11        | 2063023753 |        3 | Patching    | 03/08/2018 | 05/08/2018    |      0.25 |
  | HA11        | 2012358108 |        4 | Plan        | 03/08/2018 | 05/08/2018    |      0.25 |
  | HA11        | 2074311499 |        4 | Dev         | 30/07/2018 | 05/08/2018    |         1 |
  | HA11        | 2075770157 |        4 | Patching    | 01/08/2018 | 05/08/2018    |      0.75 |
  | HA11        | 2059475039 |        4 | Patching    | 03/08/2018 | 05/08/2018    |      0.75 |
  | HA11        | 2062057110 |        4 | Plan        | 30/07/2018 | 05/08/2018    |      0.25 |
  | HA11        | 2043715055 |        4 | * Misc.     | 02/08/2018 | 05/08/2018    |      0.25 |
  +-------------+------------+----------+-------------+------------+---------------+-----------+
  </code></pre>
  
  <p>The query I have written that works is here:</p>
  
  <pre><code>SELECT 
      a.TM_StaffName AS [Staff], Nz(SUM(p.PS_Points), 0) AS [Total]
  FROM 
      STAFF_TABLE AS a
  LEFT JOIN 
      WORK_TABLE AS p ON (((a.TM_StaffID = p.PS_Staff) 
                      AND cdate(ps.PS_Date) &gt;= #2018-07-30#) 
                      AND cdate(ps.PS_Date) &lt;= #2018-08-05#)
  WHERE 
      a.TM_TeamName = 'HA11'
  GROUP BY 
      a.TM_StaffName;
  </code></pre>
  
  <p>The problem is, moving to SQL Server, that <code>CDate</code> isn't recognised as a function and fails the query. Removing the <code>CDate</code> brackets then says that the "JOIN function is not supported". I have also tried keeping the # instead of <code>'</code> around the dates but this returns zero.</p>
  
  <p>It seems to be a strange quirk but removing the <code>CDate</code> from the columns breaks the query as an unsupported join in MS Access. </p>
  
  <p>Essentially, is there a better way to write this query so that ALL the staff names from the left table appear and if there are no matching records in the right table, it displays a zero?</p>
  
  <p>An efficient and easy way of writing this results in the below tables returning 3 names on the left and 3 totals. But PS_Staff '2' should also display and show 0 instead of not appearing at all.</p>
  
  <pre><code>+---------+-------+
  |  Staff  | Total |
  +---------+-------+
  | Paul A  |  1.75 |
  | Simon A |  1.75 |
  | Peter A |  3.25 |
  +---------+-------+
  
  (Need John A - 0 to show in here)
  </code></pre>
  
  <p>How can I achieve this to work with SQL Server and place the query in MS Access VBA?</p>
  
  <p>Thanks</p>