SteveL13
asked on
Delete all but one duplicate record in a table
I have a table that contains duplicate records. I want to somehow delete all but one of the duplicate records. Although it is not the key field, the field that contains the duplicate values is "Account No". How can I delete all but one of the records?
Assuming you only have the one field [Account No], then you could simply do a group by query and then change it to a make table query. Much simpler than deleting the duplicates.
However, if you have other fields, it becomes more difficult. How do you know which of the duplicates to delete? Do you only want to keep the most recent record for each [Account No] field, if so, how do you determine "most recent".
However, if you have other fields, it becomes more difficult. How do you know which of the duplicates to delete? Do you only want to keep the most recent record for each [Account No] field, if so, how do you determine "most recent".
I have a table that contains duplicate recordsOne assumes they are complete duplicates and there is nothing to differentiate them.
Throw a button on a form.
Code behind the button
Private Sub cmdRemoveAllButTwo_Click()
dim rs as Recordset
dim rs1 as recordset
set rs = Currentdb.OpenRecordset("S
'great, we have all the Account Nos
'Now walk down that set
Do until rs.eof
set rs1 = Currentdb.OpenRecordset("S
if rs1.RecordCount < 3 then
goto done
else
rs1.movenext 'skip one
rs1.movenext 'skip two
do until rs1.eof
rs1.delete 'kill it
rs1.movenext
loop
end if
done:
rs1.close
set rs1 =nothing
rs.movenext
loop
msgbox "done!"
end sub
It's air-code, but it ought to do the trick.
Get all the [Account No] values
Open a recordset of the table where the [account no] equals each value in turn.
Skip two if there are that many.
Delete the rest
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can't help you as far as the Access answer, but here's the SQL Server answer on how to pull that off: SQL Server Deleting Duplicate Rows , with lots of code examples and images. If the article helps you please click the big green 'Was this article helpful?' button at the end.
No idea if Access allows Common Table Expressions (CTE's). Perhaps if you wish to go down this road one of the Access experts can speak to that.
Good luck.
Jim
No idea if Access allows Common Table Expressions (CTE's). Perhaps if you wish to go down this road one of the Access experts can speak to that.
Good luck.
Jim
just a question! did you try my sql?
pkey accountno
1 abc
2 cde
3 abc
4 abc
5 cde
6 efg
you can try something like this!
Open in new window
of course replace tablename by your tablename, pkey with the pkey column and accountno with the account number column name..