title: Removing duplicate records from JOIN in MS Access
body: <p>My co-worker asked me for help with a query in MS Access that joins three tables.  I have confirmed that the order and inner/outer status of the <code>JOIN</code> is what my co-worker wants.  (They have three tables, A, B, and C; they want all records from table B plus the matching records from A and C.)</p>
  
  <p>The (sanitized) query is:</p>
  
  <pre><code>SELECT B.ID, B.Date from (A RIGHT JOIN B on A.ID = B.ID) LEFT JOIN C on B.ID = C.ID
  GROUP BY B.ID, B.Date
  </code></pre>
  
  <p>This returns the correct number of rows (about 16000).  However, when I change the select and group clauses to</p>
  
  <pre><code>SELECT B.ID, B.Date, A.Time ...
  GROUP BY B.ID, B.Date, A.Time 
  </code></pre>
  
  <p>then the query returns duplicate records (the record count is about 19000).  How do I improve the query to eliminate the duplicates?</p>
  
  <p><a href="https://stackoverflow.com/a/3578160/2615836" title="A: SQL: Need to remove duplicate rows in query containing multiple joins">This Stack Overflow answer</a> helped me figure out the <code>GROUP BY</code> clause for table B.  I had tried the clause as just <code>GROUP BY B.ID</code>, but got an error message that I hadn't done any aggregation with <code>B.Date</code>.</p>