title: MS Access SQL Records Disappearing in LEFT JOIN that shouldn't
body: <p>I am really not understanding why this <code>LEFT JOIN</code> is not working the way it should. There are supposed to be 99 records coming from <code>cleaning_zzz_2016</code> as is the case when I remove the <code>LEFT JOIN</code>. However, when I add the <code>LEFT JOIN</code> it drops down to 96. Some of the <code>CoNumber</code> don't exist in the <code>cleaning_zzz_sales</code> table, so I would expect the those fields to just be blank and the <code>s.</code> data to remain; however, it is removing them. I think this may be due to the <code>WHERE s.Rep = 'ZZZ'</code>, but I am not 100% sure or how to get around the issue.</p>
  
  <pre><code>SELECT j.CoNumber, j.CoName, s.Selling, s.Positions, s.Plans, '' AS Review, j.St, j.CE, j.SI, j.Tgt, ROUND(MAX(j.Comm) * 100) AS [Max of Comm], ROUND(MAX(j.Comm2) * 100) AS [Max of Comm2]
  FROM cleaning_zzz_2016 AS j 
  LEFT JOIN cleaning_zzz_sales AS s 
    ON j.CoNumber= s.CoNumber
  WHERE s.Rep = 'ZZZ' AND s.RepYear = 2016
  GROUP BY j.CoNumber, j.CoName, j.St, j.CE, j.SI, j.Tgt, s.Selling, s.Positions, s.Plans
  ORDER BY j.CoNumber;
  </code></pre>