Solved

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

Posted on 2013-01-16
4
544 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
  • 2
  • 2
4 Comments
 
LVL 73

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 73

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Outlook Free & Paid Tools
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now