Search code examples
sqlms-access

Selectively deleting duplicates from table based on field value in Access

I have the following table (ID column exists but not shown below) :

Email Course DateComplete
[email protected] Running 01/01/2021
[email protected] Running
[email protected] Running
[email protected] Walking
[email protected] Walking
[email protected] Walking

I'd like to know if it is possible to delete all duplicate (of Email&Course) records from my table, but also ensuring that no records with a value in DateComplete are deleted.

So after running the query I would have :

Email Course DateComplete
[email protected] Running 01/01/2021
[email protected] Walking
Solution

  • You just need a query with an aggregation such as

    SELECT Email, Course, MAX(DateComplete) AS DateComplete
  INTO [dbo].[new_table]
  FROM [dbo].[current_table]
 GROUP BY Email, Course