Link to home
Start Free TrialLog in
Avatar of Ridequality
Ridequality

asked on

Foltering duplicates on a table

I have a table with 40k records.
the table has 16 fileds as well.
the first field is an auto number with no duplicates.
the second field is no duplicate field as well.

Using the data in fields 3 thru 6 and fields 9 thru 11
I would like to remove the duplicate records.

thank you for your help.

Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Hi Ridequality,

**backup before using this**

DELETE t1.*
FROM YourTable AS t1
WHERE t1.f1 Not In
    (SELECT Min(t2.f1)
    FROM YourTable AS t2
    GROUP BY t2.f3, t2.f4, t2.f5, t2.f6, t2.f9, t2.f10, t2.f11)

Regards,

Patrick
easiest way:   Copy table, structure only.
Add index to new table which includes fields 3-6 and 9-11.
Make index unique.
Append all records from old table to new table.  Your index unique rule will prevent dupes from being appended.
Ridequality,

Or, make a new table with just the single instances:

SELECT t1.*
INTO NewTable
FROM YourTable AS t1
WHERE t1.f1 In
    (SELECT Min(t2.f1)
    FROM YourTable AS t2
    GROUP BY t2.f3, t2.f4, t2.f5, t2.f6, t2.f9, t2.f10, t2.f11)

Regards,

Patrick
I like it Jerry:)
Wondering which data from fields 1-2, 7-8 & 12-16 you want to keep.  Or does it matter?
Avatar of Ridequality
Ridequality

ASKER

All fields would be kept, its the actual record that will be eliminated.
I will test out your answers when i get back to the office.
What i meant was, if you have a dupe of fields 3-6 & 9-11, but diffferent data in
1, 2, etc, which of the two would you keep?  If it is not important, the method I mentioned will work just fine.
Just to clarify my question a little bit further:
I am trying to eliminate what i consider to be duplicate data in my database.
I just want the records that are unique, based on there matching content in those fields.
 
Have you tried any of the suggestions yet?
Not yet, i will be back in the office shortly.
I am loooking thru the answers given to me, but am not really sure how to use them..
I am a complete rookie at this..
And I'm afraid to even post because I have no idea what 'foltering duplicates' is all about:)
Patrick, i am not quite sure what I should do with the formula you gave me above, could you let me know?
Also, JerryB30, I did try your method only to find that it would only coppy 2 of the records and says it cannot copy the rest due to :"key violations" can you point me in the right direction on that>?
JerryB30- I found the problem on that one, but I also found that some of the records have a null value for the fields I am using to eliminate the duplicates.
So I dont beleive that will work for me.
you may have to do this using recordset

create a Find Duplicate query using the wizard
saved your duplicate query as qryDuplicates
make sure that you have an Order By clause in your query

Create a form with a command button cmdDeleteDuplicates


Private Sub cmdDeleteDuplicates_Click()
On Error Resume Next

  Dim DB As DAO.Database, rst As DAO.Recordset
  Dim strDupName As String, strSaveName As String
 
  Set DB = CurrentDb()
  Set rst = DB.OpenRecordset("qryDuplicates")
   
  If rst.BOF And rst.EOF Then
    MsgBox "No records to process"
  Else
    rst.MoveFirst
    Do Until rst.EOF
      strDupName = rst.Fields(0 & rst.Fields(1) & rst.Fields(2)   ' add more fields if required
      If strDupName = strSaveName Then
        rst.Delete
      Else
        strSaveName = rst.Fields(0) & rst.Fields(1) & rst.Fields(2)
      End If
      rst.MoveNext
    Loop  
    Set rst = Nothing
    Set DB = Nothing    
     End If
End Sub

NOTE: make a copy of your table before you do this

        rst.Fields(0) means the  first field from your query
       you can also use
             rst(0)
       or
            rst("NameOfField")





         
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial