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.

RidequalityAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
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
jerryb30Commented:
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.
Patrick MatthewsCommented:
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
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

GRayLCommented:
I like it Jerry:)
jerryb30Commented:
Wondering which data from fields 1-2, 7-8 & 12-16 you want to keep.  Or does it matter?
RidequalityAuthor Commented:
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.
jerryb30Commented:
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.
RidequalityAuthor Commented:
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.
 
Patrick MatthewsCommented:
Have you tried any of the suggestions yet?
RidequalityAuthor Commented:
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..
GRayLCommented:
And I'm afraid to even post because I have no idea what 'foltering duplicates' is all about:)
RidequalityAuthor Commented:
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>?
RidequalityAuthor Commented:
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.
Rey Obrero (Capricorn1)Commented:
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")





         
Patrick MatthewsCommented:
Ridequality,

I provided two ways of doing it.  The first deletes the "extra" rows from your table:

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)


The second creates a new table with only the "first" instance of each entry from the original table:

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)


To use either, create a new query in Design View, close the dialog to add tables, and switch to the SQL View.
Replace 'YourTable' with whatever the name of your original table is.  If you use #2, replace NewTable with
what you want the result table to be named.  In either case, replace f1, f2, f3 etc with the actual names of
your fields.

Both queries assume that the "important" data are in f2, f3, f4, f9, f10, and f11, and that the records you
want to keep are the ones with unique values in those fields as a whole, taking the record with the lowest
value in f1 (your AutoNumber field).

After doing the renaming, click the ! icon to run.

If you use #1, I strongly urge you to make a copy of your table first.

Patrick

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.