Solved

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

Posted on 2013-01-16
4
549 Views
Last Modified: 2013-01-16
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?
0
Comment
Question by:geeta_m9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38784223
DELETE FROM yourtable
      WHERE (firstname, lastname, midlename,  entrydate) NOT IN
                (SELECT   firstname, lastname, midlename,  MAX(entrydate)
                     FROM yourtable
                 GROUP BY firstname, lastname, midlename)
0
 

Author Comment

by:geeta_m9
ID: 38784229
I understand the SQL syntax, but how do I accomplish the same thing using the Query Design feature in MS-Access?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38784237
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
 

Author Closing Comment

by:geeta_m9
ID: 38784301
I just tried it out and it works great!

You're amazing...thank you!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question