How large is your dataset, if you are bound and determined to delete records why not just use the find duplicates query wizard? then cycle through them manually to delete them (if this is a one time project and you're just trying to combine separate datasets).
This is what I did.
Create a table called Tbl_DupCheck
Create 5 Fields
Item_ID (autonumber)
Item_Date (date/time)
Item_Text (text)
Item_Number (number)
Item_Delete (yes/no)
Populate it with this data:
Tbl_DupCheck Item_ID Item_Date Item_Text Item_Number Item_Delete
1 1/1/2011 TESTTEXT 1 No
2 1/1/2011 TESTTEXT 1 No
3 1/1/2011 TESTTEXT 2 No
4 1/1/2011 TESSTTEXT 2 No
5 1/1/2011 TESTTEXT 2 No
Create this query:
Code:
SELECT Tbl_DupCheck.Item_Date, Tbl_DupCheck.Item_Text, Tbl_DupCheck.Item_Number, Tbl_DupCheck.Item_ID
FROM Tbl_DupCheck
WHERE (((Tbl_DupCheck.Item_Date) In (SELECT [Item_Date] FROM [Tbl_DupCheck] As Tmp GROUP BY [Item_Date],[Item_Text],[Item_Number] HAVING Count(*)>1 And [Item_Text] = [Tbl_DupCheck].[Item_Text] And [Item_Number] = [Tbl_DupCheck].[Item_Number])))
ORDER BY Tbl_DupCheck.Item_Date, Tbl_DupCheck.Item_Text, Tbl_DupCheck.Item_Number, Tbl_DupCheck.Item_ID;
Call it Qry_DupCheck_Duplicates
Run this code:
Code:
Dim iCurrID As Long
Dim dCurrDate As Date
Dim sCurrText As String
Dim iCurrNumber As Long
Dim iPrevID As Long
Dim dPrevDate As Date
Dim sPrevText As String
Dim iPrevNumber As Long
Set db = CurrentDb
Set rst_CheckList = db.OpenRecordset("Qry_DupCheck_Duplicates")
rst_CheckList.MoveFirst
Do While rst_CheckList.EOF <> True
iCurrID = rst_CheckList.Fields(3)
dCurrDate = rst_CheckList.Fields(0)
sCurrText = rst_CheckList.Fields(1)
iCurrNumber = rst_CheckList.Fields(2)
Debug.Print iCurrID
If dCurrDate = dPrevDate And sCurrText = sPrevText And iCurrNumber = iPrevNumber Then
sSQL = "UPDATE Tbl_DupCheck SET Item_Delete = -1 WHERE ((Item_ID) = " & iCurrID & ")"
db.Execute sSQL
Debug.Print sSQL
End If
iPrevID = iCurrID
dPrevDate = dCurrDate
sPrevText = sCurrText
iPrevNumber = iCurrNumber
rst_CheckList.MoveNext
Loop
It will mark all of the potential duplicates with a checkmark in the ITEM_DELETE field. From there you can determine whether or not you want to delete everything that is marked yes or no. As I said I'm very against deleting data unless it's absolutely necessary preferring instead ot use a field that you can void a record and set criteria for queries to ignore records marked yes.
This is my data after I ran this code:
Tbl_DupCheck Item_ID Item_Date Item_Text Item_Number Item_Delete
1 1/1/2011 TESTTEXT 1 No
2 1/1/2011 TESTTEXT 1 Yes
3 1/1/2011 TESTTEXT 2 No
4 1/1/2011 TESSTTEXT 2 No
5 1/1/2011 TESTTEXT 2 Yes
Windows 7 32bit 
Access 2007
Reply With Quote
i may get here
