Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

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?
  • 2
  • 2
1 Solution
DELETE FROM yourtable
      WHERE (firstname, lastname, midlename,  entrydate) NOT IN
                (SELECT   firstname, lastname, midlename,  MAX(entrydate)
                     FROM yourtable
                 GROUP BY firstname, lastname, midlename)
geeta_m9Author Commented:
I understand the SQL syntax, but how do I accomplish the same thing using the Query Design feature in MS-Access?
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)
geeta_m9Author Commented:
I just tried it out and it works great!

You're amazing...thank you!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now