MS Access SQL query a table and remove duplicates

I have reviewed several posts here and can’t find the answer, but it may be that the problem and answer are a bit above my pay grade.

I have an MSAccess Table with two ID fields (ID1 and ID2), and many other columns.

I want to query (SELECT statement) the table and need two things to happen. Not sure if the order matters. I want the query to return all columns from the table.

No duplicates for ID1, and I simply don’t care which record(s) gets thrown away.
No duplicates for ID2, and I want to keep the record where the Date field in the table is earlier than the other values for records with the same ID2 value.

Just can’t figure out how to do this with an SQL Select statement.

Example Data – assume DateFld is ASC from top to bottom

ID1    ID2    DateFld    ...
1      24
1      24
2      23
3      98
4      23
4      23
5      98
6      72

Keep rows 1, 3, 4, 8

No indices, as this table was created from a make table query; but I can add them. Let me know if you need more.

Answer

For this sample data you can use NOT EXISTS:

SELECT t.*
FROM tablename AS t
WHERE NOT EXISTS (
  SELECT 1 FROM tablename
  WHERE ID2 = t.ID2 AND DateFld < t.DateFld
)

Results:

ID1 ID2 DateFld
1   24  ...
2   23  ...
3   98  ...
6   72  ...

Advertisement

Answer