Delete duplicate entries

Posted on 2005-04-13
Last Modified: 2007-12-19
I have a table with approx 900 entries, 300 of which are duplicates..... how do I delete them?

Question by:galneweinhaw
    LVL 30

    Assisted Solution

    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.
    LVL 77

    Expert Comment

    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.

    Author Comment

    I can't delete and rename my new table because of all the relationships that exists and 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 =(
    LVL 18

    Assisted Solution

    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)


    LVL 5

    Expert Comment


    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

    LVL 7

    Expert Comment

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

    Expert Comment

    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".
    LVL 7

    Expert Comment

    I forget... If your table already have an unique key, you don't need to add the key-column.
    LVL 3

    Accepted Solution

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now