Solved: Removing Duplicates from a table

[Slicemahn]

Hello Everyone!

I sure hope someone can help with problem. I have a table in which I need to remove duplicates. However in doing so, I must keep the record in which the customers account # and enrollment date is the latest or max.

Here are the fields:

Comp#
Acct#
CustomerAcct# (equals Comp#+Acct#)
Status
StartDate
StopDate

Sample data

0002 00000314123 000200000314123 A 11/11/2005 11/18/2006
0002 00000314123 000200000314123 A 10/03/2003 6/1/2007
0002 00000314123 000200000314123 A 10/01/2005 11/28/2007

How do I purge my table of all duplicates and keep only the

002 00000314123 0002000031423 A 10/01/2005 11/28/2007?

Many thanks for suggestions on this problem.

[asingh]

Hi,

attached is a sample db...

1. qry_select_max_dates: will select the max dates with the CustomerAcct# field as the grouping field.

2. qry_select_max_data: will pull out the max dated accounts.


regards,

asingh

[DarkSprout]

This will do the Biz...

OneField will look for Duplicates in just the one named field, if left out - it will look for Duplicates accross all fields.


Public Function DeleteDuplicateRecords(strTableName As String, Optional OneField 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.
If OneField = "" Then
For Each fld In tdf.Fields
If (fld.Type <> dbMemo) And (fld.Type <> dbLongBinary) Then
strSQL = strSQL & "[" & fld.name & "], "
End If
Next fld
Else
strSQL = strSQL & "[" & OneField & "], "
End If
' 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 Function