delete duplicate records from table with two keys

pkromer
pkromer used Ask the Experts™
on
Hi. I need to delete records from a table which should have two keys: OrderNumber and LocalSKU.

As of now, the table has many duplicates with both those fields. What I need to do is delete dupes so I end up with just one record for each time the record contains a OrderNumber and the LocalSKU unique.

I know I'm not explaining this right so here is a sample of the data...

OrderNumber LocalSKU
89120      141
89120      141
89120      141
89201      490
89201      490
89201      490
89201      490
89201      490
89201      490
89201      490
89201      490
89201      490
89525      2883
89525      2883
89726      446
89726      446
89726      447
89726      447
89726      8212
89726      8212
91989      601
91989      601
92138      1756
92138      1756

As you can see, OrderNumber 92138 (for example) has 2 records. I need to have just one record. Since I can't go off one primary key field, I don't know how to do this. Thanks for your help in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
do you have a unique recordID?

see this link
How to delete duplicate records from a table in Access
http://support.microsoft.com/?kbid=209183

Author

Commented:
No, that’s the point, there are 2 identifiers in those records. I do know how to do a delete when there is one key, but not two.
Top Expert 2016
Commented:
did you see the instruction from the link i posted?
Top Expert 2016

Commented:
assuming the name of your table is yrTable
run this two queries

1. Alter table yrTable add column ID counter


2.
DELETE yrTable.*, yrTable.ID
FROM yrTable
WHERE (((yrTable.ID) Not In (select min(ID) from yrTable X where OrderNumber=yrTable.OrderNumber and LocalSKU=yrtable.LocalSKU)));

Author

Commented:
I glanced at it before and thought it was the same thing that I already knew. It did outline exactly what I needed, and it worked. Thanks much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial