Solved

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

Posted on 2013-01-16
4
545 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 4 45
poor performance from  MySQL stored procedure 6 23
Compile Error 7 41
What query can i write to find where a function is 4 21
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
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…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

911 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

22 Experts available now in Live!

Get 1:1 Help Now