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

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 :

CodePudding user response：

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 

CodePudding user response：

Run a loop that deletes the dupes:

Public Function DeleteDupes()

    Const Sql   As String = "Select * From Course Order By Email, Course, DateComplete Desc"
    
    Dim Records As DAO.Recordset
    Dim Values  As String
    
    Set Records = CurrentDb.OpenRecordset(Sql)
    
    While Not Records.EOF
        If Values <> Records!Email.Value & Records!Course.Value Then
            Values = Records!Email.Value & Records!Course.Value
        Else
            Records.Delete
        End If
        Records.MoveNext
    Wend
    Records.Close
    
End Function