Removing Dups

  • Thread starter Thread starter Powderfinger
  • Start date Start date
P

Powderfinger

I have a table with 100,000 records in it. It has two fields which I'd like
to set as the primary key (OrderNo and ShipNo). Problem is, when I try to
set the primary key, it gives me an error message that there are dups. Is
there any way to get rid of the dups?
 
Thanks Roger, although there's something wrong with my query though because
when I ran it, every record except one was deleted.
If you have time, I'd appreciate it if you looked it over :

Access 2003 under Windows 2000.

Thanks in advance

Jack


Table Name : COE_SHIP_1
Index : OINDEX (autonumber) I added this field and made it the primary key
per your instructions on website.
Old Index : combination of ORDER(6 character text field) and
SHIPMENT_NO(integer) these are the field that I want to make the primary key
but I can't because of the dups. The whole records aren't dups, just the two
fields.
-----------------

Dim dbs As Database, strSQL As String, strCriteria1 As String, strCriteria2
As String
Set dbs = CurrentDb

strCriteria1 = "SELECT MIN(COE_SHIP_1.OINDEX) FROM COE_SHIP_1 WHERE "

strCriteria2 = "((COE_SHIP_1.ORDER = COE_SHIP_1.ORDER) AND
(COE_SHIP_1.SHIPMENT_NO = COE_SHIP_1.SHIPMENT_NO))"

strSQL = "DELETE * FROM COE_SHIP_1 WHERE COE_SHIP_1.OINDEX > (" &
strCriteria1 & strCriteria2 & ")"

dbs.Execute (strSQL)
 
You need to alias the table in your subquery. See the Xs in the following
query and compare it to yours:

strCriteria1 = "SELECT MIN(COE_SHIP_1.OINDEX) FROM COE_SHIP_1 X WHERE "

strCriteria2 = "((COE_SHIP_1.ORDER = X.ORDER) AND
(COE_SHIP_1.SHIPMENT_NO = X.SHIPMENT_NO))"

strSQL = "DELETE * FROM COE_SHIP_1 WHERE COE_SHIP_1.OINDEX > (" &
strCriteria1 & strCriteria2 & ")"


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Back
Top