Removing Dups

[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?

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "RemoveDuplicates.mdb" which illustrates how to do this.

--
--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

 

[Powderfinger]

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)

 

[Roger Carlson]

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