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 JOIN 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.)
The (sanitized) query is:
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
This returns the correct number of rows (about 16000). However, when I change the select and group clauses to
SELECT B.ID, B.Date, A.Time ...
GROUP BY B.ID, B.Date, A.Time
then the query returns duplicate records (the record count is about 19000). How do I improve the query to eliminate the duplicates?
This Stack Overflow answer helped me figure out the GROUP BY clause for table B. I had tried the clause as just GROUP BY B.ID, but got an error message that I hadn't done any aggregation with B.Date.
Is it actually producing duplicate records, or is it now returning multiple records from the same date that have different times? If so, you will need to assess if these are actually duplicate records for your report purpose. If they are, you will want to aggregate the time with something like min(a.time) or max(a.time) in the select clause (to get the earliest or latest instance only) and leave it out of the group by.