Creating a delete query to delete duplicate records in MS-Access

I would like to create a query which will delete duplicate records in a table, i.e., those records having the same First Name, Last Name and Middle Name but having an earlier entry date/time.

For example, if have the following records in a table:

First Name  Last Name   Middle Name      Email                         Entry Date

1  John          Smith          Peter                jsmith@email.com      01/08/2013 11:15:36 AM
2. John          Smith          Peter                jsmith@email.com      01/06/2013 12:40:03 PM  
3. Jane          Doe             Mary                 janedoe@email.com   12/10/2013 10:20:00 AM
4. Jane          Doe             Mary                 janedoe@email.com   12/10/2013  03:21:12 PM
5. John          Smith          Peter                jsmith@email.com      01/05/2013  05:22:14 PM

The query will delete Records 2, 3 and 5 will be deleted. How could I write such a query in Access using Query Design window and how would the SQL look like?
geeta_m9Asked:
Who is Participating?
 
sdstuberCommented:
change the query designer to sql view and enter the text there

an alternate version

DELETE FROM yourtable a
      WHERE entrydate <
                (SELECT MAX(b.entrydate)
                   FROM yourtable b
                  WHERE     a.firstname = b.firstname
                        AND a.lastname = b.lastname
                        AND a.middlename = b.middlename)
0
 
sdstuberCommented:
DELETE FROM yourtable
      WHERE (firstname, lastname, midlename,  entrydate) NOT IN
                (SELECT   firstname, lastname, midlename,  MAX(entrydate)
                     FROM yourtable
                 GROUP BY firstname, lastname, midlename)
0
 
geeta_m9Author Commented:
I understand the SQL syntax, but how do I accomplish the same thing using the Query Design feature in MS-Access?
0
 
geeta_m9Author Commented:
I just tried it out and it works great!

You're amazing...thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.