Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

MS Access Delete Dup Records

I have a access table that contains the following fields.
IndexID, TicketNumber, ItemNumber

I want to delete duplicate records matching on the two fields Ticketnumber, ItemNumber.
Leaving One of the two matching records.
0
sfn149
Asked:
sfn149
  • 5
  • 4
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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)
0
 
Dale FyeCommented:
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)
0
 
sfn149Author Commented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:
the text posted above are sql statement for  a delete query..

create a query and use the sql statement posted above..

or if you want to run it in vba

dim sql as string

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


currentdb.execute sql,dbfailonerror





0
 
sfn149Author Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
post the codes you are using...

check for correct table name and field names, check for correct spelling too.
0
 
Rey Obrero (Capricorn1)Commented:
better create a blank .MDB file, import the table "Pawns"

upload the .MDB that was created..
0
 
sfn149Author Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
the codes looks good,

check for correct table name and field names, check for correct spelling too.
0
 
Dale FyeCommented:
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

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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now