• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5453
  • Last Modified:

Delete duplicate entries

I have a table with approx 900 entries, 300 of which are duplicates..... how do I delete them?

3 Solutions
I would recommend

1. Performing a select query with select distinct on the table to remove the duplicates, then put the results of that into another table.

2. Then you can delete your original table and replace the ones from your de-duped table.

To select distinct, create a query which selects everything from the table, then ensure 'Unique Records' is set to yes for the query.

Then you can use that query to feed an Append or Make Table query.
Hi galneweinhaw,
What identifies them as duplicates?
A single field value?
A combination of fields? (how many?)

Create a copy of the table structure; then make the (combination of )  field(s) that identifies the duplicate a unique index; then run an append query from the existing table to the new table.  All duplicates will be rejected.  You can then rename the tables.
galneweinhawAuthor Commented:
I can't delete and rename my new table because of all the relationships that exists and stuff...it says I can't delete the old table.  And I sure don't want to have to re-establish the web of relations going on there =(
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Using mcdermaid's step 1, don't delete the whole original table, just delete all the records, and then append your new table's records back into the original. That would save you deleting the table itself (and breaking relationships)



you can try this

if all the fields in the table are duplicate then

1. group the field using Query.
2. change the group query to Make table Query and save it in a table
3. delete your old table

try this

Hi, is it possible that you add an column to your table? If so, I can post you a solution based on one SQL-Statement.
Add a new column "key" type autovalue. Now all your table entries have an unique key (1...n)

Now try this SQL:

DELETE * FROM tblOrig WHERE Key not in
(SELECT First(Key) AS KeyNotDel FROM tblOrig GROUP BY Field1, Field2, Field3);

tblOrig is your table. The Fields in the "GROUP BY"-clause reflects all fields which can hold duplicate entries. If all table-columns must contain duplicate values, add all columns to the "GROUP BY"-clause.

After executing the query, you can delete the "key" column. If you work in a multi-user environment and you (or the users) need the upper query often, leave it in the table.

The query performs slow, so it is useful to add an index to the field "key".
I forget... If your table already have an unique key, you don't need to add the key-column.
This will solve your problems:

Sub DeleteDuplicateRecords(strTableName As String)
    ' Deletes exact duplicates from the specified table.
    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 _
         <>  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
    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
        GoTo SkipBookmark
        rst2.Bookmark = varBookmark
End Sub


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now