Re: Removing Dups

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

"Powderfinger" <PFinger@xxxxxxx> wrote in message
news:2NSdnXlJvJqCudbbnZ2dnUVZ_revnZ2d@xxxxxxxxxxxxxx
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" <RogerCarlson@xxxxxxxxxxxxxxx> wrote in message
news:OFygfz7lHHA.568@xxxxxxxxxxxxxxxxxxxxxxx
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" <PFinger@xxxxxxx> wrote in message
news:Rvmdnbcuoc38ntbbnZ2dnUVZ_gCdnZ2d@xxxxxxxxxxxxxx
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?








.

Relevant Pages

  • Re: RunSQL Update within DAO
    ... --Roger Carlson ... MS Access MVP ... Access Database Samples: www.rogersaccesslibrary.com ... dim strSQL as String ...
    (microsoft.public.access.formscoding)
  • Re: Percent Syntax not working...
    ... just want SUM. ... --Roger Carlson ... MS Access MVP ... Access Database Samples: www.rogersaccesslibrary.com ...
    (microsoft.public.access.modulesdaovba)
  • Re: QueryDef vs. Recordset Errors
    ... the DSUM function works great. ... "Roger Carlson" wrote: ... MS Access MVP ... Access Database Samples: www.rogersaccesslibrary.com ...
    (microsoft.public.access.formscoding)
  • Re: No Record Error
    ... --Roger Carlson ... MS Access MVP ... Access Database Samples: www.rogersaccesslibrary.com ... ' On Error GoTo Err_ScheduleCB_Click ...
    (microsoft.public.access.forms)
  • Re: Export Queries to Excel
    ... once the query is open i don't want them to edit any records, ... "Roger Carlson" wrote: ... DoCmd.TransferSpreadsheet acExport, 8, List209.Column, _ ... Access Database Samples: www.rogersaccesslibrary.com ...
    (microsoft.public.access.externaldata)