asked on

Delete Dupicate record in Access

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

Richard Daneke

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?

SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Daneke

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.

chaau

@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.

Fred Webb

ASKER

DoDahD, chaau is correct if i attempt to convert the SELECT to a DELETE statement it wants to delete all 6 records.

Fred Webb

ASKER

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.

Fred Webb

ASKER

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"

Fred Webb

ASKER

even selecting a as the table it errors with "could not delete form the specified table"

chaau

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

Fred Webb

ASKER

Nope, same issue "could not delete form the specified table"

chaau

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

Fred Webb

ASKER

Nope... that throws a new error, see attached file
ErrorMsg.jpg

Fred Webb

ASKER

It seems to me it should not be this difficult to delete duplicate records.... Much simpler in MSSQL.

ASKER CERTIFIED SOLUTION

Fred Webb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fred Webb

ASKER

Mine worked