MS Access Delete Dup Records

if the field IndexID is unique, try this.. backup your table first


delete t.*
from tablex as t
where indexId Not in (select min(indexid) from tablex t2 where t2.TicketNumber= t.TicketNumber and t2.ItemNumber=t.ItemNumber)

Assuming your IndexID is an autonumber, is it used as a foreign key in any other table?  If not, you could try:

DELETE FROM yourTable WHERE ID NOT IN (SELECT Min(ID) as MinID FROM yourTable GROUP BY TicketNumber, ItemNumber)

Neither is working for me, it may help to give me a little more information as to how to incorporate the above into my database.
I tried just configuring a cmd button with VB running the above as sql scripts but each example comes back asking me for values.

Ok I replaced tablex with the name of my table "Pawns"
I get an error on the last line that says
Run-Time Error '3061':
too few parameters. Expected 4.

post the codes you are using...

check for correct table name and field names, check for correct spelling too.

better create a blank .MDB file, import the table "Pawns"

upload the .MDB that was created..

Private Sub cmdDelDup_Click()
Dim sql As String

sql = "delete t.*"
sql = sql & " from Pawns as t"
sql = sql & " where IndexId Not in (select min(Indexid) from Pawns t2 where t2.TicketNumber= t.TicketNumber and t2.ItemNumber=t.ItemNumber)"

CurrentDb.Execute sql, dbFailOnError
End Sub

the codes looks good,

check for correct table name and field names, check for correct spelling too.

I would save the SQL string (either Capricorn1's or my original post) as a query (call it qry_Delete_Pawn_Dups).  Actually, in my original post I forgot to use IndexID, here is the corrected version

DELETE FROM Pawns WHERE IndexID NOT IN (SELECT Min(IndexID) as MinID FROM yourTable GROUP BY TicketNumber, ItemNumber)

Then, I would modify the click event as:

Private Sub cmdDelDup_Click

    currentdb.querydefs("qry_Delete_Pawn_Dups").Execute dbfailonerror

End Sub

I originally had one of my field names incorrect which produced an error.  After figuring that out, this worked perfect thanks