Solved

Delete Dupicate record in Access

Posted on 2013-11-20
15
392 Views
Last Modified: 2013-11-27
I have a dealer registration table that I have some duplicate records and I want to just delete the duplicates, I have created a find duplicate record query  
SELECT First(dbo_dealer_trade_expo_Bkup.[accountnumber]) AS [accountnumber Field]
FROM dbo_dealer_trade_expo_Bkup
GROUP BY dbo_dealer_trade_expo_Bkup.[accountnumber], dbo_dealer_trade_expo_Bkup.[businessname]
HAVING (((Count(dbo_dealer_trade_expo_Bkup.[accountnumber]))>1) AND ((Count(dbo_dealer_trade_expo_Bkup.[businessname]))>1));

Open in new window

which shows me the 3 records that have duplicates I created a 2nd Query to delete the duplicates using the first query in the Criteria field with the IN predicate
SELECT dbo_dealer_trade_expo_Bkup.accountnumber
FROM dbo_dealer_trade_expo_Bkup
WHERE (((dbo_dealer_trade_expo_Bkup.accountnumber) In (SELECT First(dbo_dealer_trade_expo_Bkup.[accountnumber]) AS [accountnumber Field]
FROM dbo_dealer_trade_expo_Bkup
GROUP BY dbo_dealer_trade_expo_Bkup.[accountnumber], dbo_dealer_trade_expo_Bkup.[businessname]
HAVING (((Count(dbo_dealer_trade_expo_Bkup.[accountnumber]))>1) AND ((Count(dbo_dealer_trade_expo_Bkup.[businessname]))>1));)));

Open in new window

and I run a select statement which returns all 6 records not just the 3 the first Query does
0
Comment
Question by:skull52
  • 9
  • 4
  • 2
15 Comments
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 39664275
SQL syntax for a delete is DELETE not SELECT.    Changing the initial verb in your SQL statement will delete the records found.   Are you sure you want to delete the recordset displayed by this SQL statement?
0
 
LVL 24

Assisted Solution

by:chaau
chaau earned 500 total points
ID: 39664416
I glad that you did not convert the select statement to delete statement straight away, as suggested DoDahD. Doing so would have deleted all 6 records. It is always a good idea to first run the Select statement and then convert it to delete statement.

Now, to your problem. If you need to delete just duplicate entries leaving one set in the database you need first to figure a field that is different between the duplicate entries. To figure what field is different you need to run this query:
SELECT *
FROM dbo_dealer_trade_expo_Bkup
WHERE (((dbo_dealer_trade_expo_Bkup.accountnumber) In (SELECT First(dbo_dealer_trade_expo_Bkup.[accountnumber]) AS [accountnumber Field]
FROM dbo_dealer_trade_expo_Bkup
GROUP BY dbo_dealer_trade_expo_Bkup.[accountnumber], dbo_dealer_trade_expo_Bkup.[businessname]
HAVING (((Count(dbo_dealer_trade_expo_Bkup.[accountnumber]))>1) AND ((Count(dbo_dealer_trade_expo_Bkup.[businessname]))>1)))));

Open in new window

See how I used "SELECT *"? This will return all columns. There must be a column that will stand out straight away. It could be a column called "ID", or "DateCreated" or something. When you identified this column you need to choose what entry to keep. Most of the time the entry with the latest value is considered "good". In this case you need to modify your select statement like this:
SELECT *
FROM dbo_dealer_trade_expo_Bkup a
INNER JOIN
(SELECT MAX(ID) AS goodValue, MIN(ID) AS badValue,
First(dbo_dealer_trade_expo_Bkup.[accountnumber]) AS [accountnumber Field]
FROM dbo_dealer_trade_expo_Bkup
GROUP BY dbo_dealer_trade_expo_Bkup.[accountnumber], dbo_dealer_trade_expo_Bkup.[businessname]
HAVING Count(dbo_dealer_trade_expo_Bkup.[accountnumber])>1 AND Count(dbo_dealer_trade_expo_Bkup.[businessname])>1) b 
ON a.accountnumber = b.[accountnumber Field] and a.ID = b.goodValue;

Open in new window

(you need to replace the column ID in the statement above to your column you have identified before)
The statement will show you only the three rows that are "good". To retrieve the "bad" rows, replace the last condition to a.ID = b.badValue and run the Select statement again.
Once you are happy with what you see (I mean you see only the "bad" values), convert the select statement to the delete statement, like this:
DELETE 
FROM dbo_dealer_trade_expo_Bkup a
INNER JOIN
(SELECT MAX(ID) AS goodValue, MIN(ID) AS badValue,
First(dbo_dealer_trade_expo_Bkup.[accountnumber]) AS [accountnumber Field]
FROM dbo_dealer_trade_expo_Bkup
GROUP BY dbo_dealer_trade_expo_Bkup.[accountnumber], dbo_dealer_trade_expo_Bkup.[businessname]
HAVING Count(dbo_dealer_trade_expo_Bkup.[accountnumber])>1 AND Count(dbo_dealer_trade_expo_Bkup.[businessname])>1) b 
ON a.accountnumber = b.[accountnumber Field] and a.ID = b.badValue;

Open in new window

0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 39664604
chaau, You are more elegant in your solution, but the fact remains if you are content with the 3 records from the first select, One can edit the statement replacing Select with Delete and the correct 3 records are removed.  The second SQL statement, therefore is not needed.
0
 
LVL 24

Expert Comment

by:chaau
ID: 39664613
@DoDahD: not every SELECT statement can be converted to Delete. For example the first statement from the very first cannot be converted (there will be an error), and if the second statement is converted to delete - all 6 rows will be deleted. Therefore I have asked the OP not to do so.
0
 

Author Comment

by:skull52
ID: 39665954
DoDahD, chaau is correct if i attempt to convert the SELECT to a DELETE statement it wants to delete all 6 records.
0
 

Author Comment

by:skull52
ID: 39665970
chaau, the problem i am having is that 1) there is no ID column in that table and 2) the duplicates are exactly the same in all values. I tried your suggestion  (you need to replace the column ID in the statement above to your column you have identified before) and replaced the ID with the accountnumber and it again returned all 6 rows.
0
 

Author Comment

by:skull52
ID: 39666072
OK I added an ID column to the table and the SELECT statement worked and gave me the proper rows to delete, however the delete statement gave the following error "Specify the table containing the records you want to delete"
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:skull52
ID: 39666083
even selecting a as the table it errors with "could not delete form the specified table"
0
 
LVL 24

Expert Comment

by:chaau
ID: 39667253
I think Access wants at least one column in the delete statement. Also, Access wants DISTINCTROW to be added to the delete statement. Try this:
DELETE DISTINCTROW dbo_dealer_trade_expo_Bkup.*
FROM dbo_dealer_trade_expo_Bkup
INNER JOIN
(SELECT MAX(ID) AS goodValue, MIN(ID) AS badValue,
First(dbo_dealer_trade_expo_Bkup.[accountnumber]) AS [accountnumber Field]
FROM dbo_dealer_trade_expo_Bkup
GROUP BY dbo_dealer_trade_expo_Bkup.[accountnumber], dbo_dealer_trade_expo_Bkup.[businessname]
HAVING Count(dbo_dealer_trade_expo_Bkup.[accountnumber])>1 AND Count(dbo_dealer_trade_expo_Bkup.[businessname])>1) b 
ON dbo_dealer_trade_expo_Bkup.accountnumber = b.[accountnumber Field] and dbo_dealer_trade_expo_Bkup.ID = b.badValue;

Open in new window

0
 

Author Comment

by:skull52
ID: 39667417
Nope, same issue "could not delete form the specified table"
0
 
LVL 24

Expert Comment

by:chaau
ID: 39667440
OK, then Access is confusing with all the joins. Use this as a last resort:
DELETE DISTINCTROW dbo_dealer_trade_expo_Bkup.*
FROM dbo_dealer_trade_expo_Bkup
WHERE ID IN
(SELECT dbo_dealer_trade_expo_Bkup.ID FROM
INNER JOIN
(SELECT MAX(ID) AS goodValue, MIN(ID) AS badValue,
First(dbo_dealer_trade_expo_Bkup.[accountnumber]) AS [accountnumber Field]
FROM dbo_dealer_trade_expo_Bkup
GROUP BY dbo_dealer_trade_expo_Bkup.[accountnumber], dbo_dealer_trade_expo_Bkup.[businessname]
HAVING Count(dbo_dealer_trade_expo_Bkup.[accountnumber])>1 AND Count(dbo_dealer_trade_expo_Bkup.[businessname])>1) b 
ON dbo_dealer_trade_expo_Bkup.accountnumber = b.[accountnumber Field] and dbo_dealer_trade_expo_Bkup.ID = b.badValue);

Open in new window

0
 

Author Comment

by:skull52
ID: 39668788
Nope... that throws a new error, see attached file
ErrorMsg.jpg
0
 

Author Comment

by:skull52
ID: 39668797
It seems to me it should not be this difficult to delete duplicate records.... Much simpler in MSSQL.
0
 

Accepted Solution

by:
skull52 earned 0 total points
ID: 39668861
OK,
I took chaau suggestion and simplified it a little to this
DELETE *
FROM dbo_dealer_trade_expo_Bkup AS T1
WHERE ID <>
   (SELECT MIN(ID)
    FROM dbo_dealer_trade_expo_Bkup AS T2
    WHERE T2.accountnumber = T1.accountnumber);

Open in new window

and that worked perfectly, the key was having a unique ID column which was not in the original table. I am going to award the points to chaau because he lead me to the solution.
0
 

Author Closing Comment

by:skull52
ID: 39680230
Mine worked
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

910 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

18 Experts available now in Live!

Get 1:1 Help Now