Delete Duplicate Records in recordset

[raa44634]

I can create the query to show the duplicate records in a table. Using that as the recordset, can someone show me the code that will go through the recordset and delete all duplicate records Except the first one in each group of matching records? Thanks a mil!

 

[mikey69]

The technique you need is described in the help file. it involves creating a blank table with the field which contains the duplicates set as primary key, then runnnig an append query using your existing data.

Search the help file for more details.

 

[raa44634]

Yep! Thanks again!

 

[cmmrfrds]

You can do it in one query provided that there is a unique id on the table, such as, an auto number.

delete from yourtable outt
where outt.rowid >
(select first(inn.rowid)
from yourtable inn
where outt.yourdupkey = inn.yourdupkey)

 

[paron]

Try

http://www.15seconds.com/issue/011009.htm

for a simple, comprehensive tutorial article on handling duplicate records.

Includes code, about 5 lines of it minus the copious comments. You need to remove the comments for Jet SQL; otherwise works fine. Makes it look as easy as you suspected it had to be.

 

[PSKELLIGAN]

Try this code . I found it in on line and it works great for me.

Sub DeleteDuplicateRecords(strTableName As String)
' Deletes exact duplicates from the specified table.
' No user confirmation is required. Use with caution.
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
Dim varBookmark As Variant

Set tdf = DBEngine(0)(0).TableDefs(strTableName)
strSQL = "SELECT * FROM " & strTableName & " ORDER BY "
' Build a sort string to make sure duplicate records are
' adjacent. Can't sort on OLE or Memo fields,though.
For Each fld In tdf.Fields
If (fld.Type <> dbMemo) And (fld.Type <> dbLongBinary) Then
strSQL = strSQL & fld.Name & ", "
End If
Next fld
' Remove the extra comma and space from the SQL
strSQL = Left(strSQL, Len(strSQL) - 2)
Set tdf = Nothing

Set rst = CurrentDb.OpenRecordset(strSQL)
Set rst2 = rst.Clone
rst.MoveNext
Do Until rst.EOF
varBookmark = rst.Bookmark
For Each fld In rst.Fields
If fld.Value <> rst2.Fields(fld.Name).Value Then
GoTo NextRecord
End If
Next fld
rst.Delete
GoTo SkipBookmark
NextRecord:
rst2.Bookmark = varBookmark
SkipBookmark:
rst.MoveNext
Loop
End Sub


Good Luck
Patrick