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.
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.
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.
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
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?
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.
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.
1, 2, etc, which of the two would you keep? If it is not important, the method I mentioned will work just fine.
ASKER
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.
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?
ASKER
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..
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:)
ASKER
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>?
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>?
ASKER
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.
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("qryDupli cates")
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")
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("qryDupli
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
**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