Delete Duplicates

[shushu]

Dear All

I have a table that has many duplcates that I need to remove. I want to keep one of the duplicates and remove all others.

The table contains many columns that need to be comapred to esnure that exact diuplcates are removed.

I have run the following query:

SELECT OppPartTemp.OppProduct_ID, OppPartTemp.Opportunity_ID, OppPartTemp.StockPart_ID, OppPartTemp.SupplierPer_ID, OppPartTemp.SupplierGroup_ID, OppPartTemp.SupplierOrg_ID, OppPartTemp.Manufacturer_ID, OppPartTemp.AddDate, OppPartTemp.AddUser, OppPartTemp.ChangeDate, OppPartTemp.ChangeUser, OppPartTemp.PartNum, OppPartTemp.CustomerPartNum, OppPartTemp.BuyCurrencyCode, OppPartTemp.BuyPrice, OppPartTemp.Margin, OppPartTemp.Qty, OppPartTemp.QtyNeeded, OppPartTemp.SellCurrencyCode, OppPartTemp.ABPPrice, OppPartTemp.SellPrice, OppPartTemp.IsChosen, OppPartTemp.IsApplyDuty, OppPartTemp.IsFreight, OppPartTemp.IsCreditCard, OppPartTemp.Delivery, OppPartTemp.DeliveryUnits, OppPartTemp.DeliveryDays, OppPartTemp.SupplierNotes, OppPartTemp.RSChangeDate, OppPartTemp.RSArriveDate, OppPartTemp.RSSiteID
FROM OppPartTemp
GROUP BY OppPartTemp.OppProduct_ID, OppPartTemp.Opportunity_ID, OppPartTemp.StockPart_ID, OppPartTemp.SupplierPer_ID, OppPartTemp.SupplierGroup_ID, OppPartTemp.SupplierOrg_ID, OppPartTemp.Manufacturer_ID, OppPartTemp.AddDate, OppPartTemp.AddUser, OppPartTemp.ChangeDate, OppPartTemp.ChangeUser, OppPartTemp.PartNum, OppPartTemp.CustomerPartNum, OppPartTemp.BuyCurrencyCode, OppPartTemp.BuyPrice, OppPartTemp.Margin, OppPartTemp.Qty, OppPartTemp.QtyNeeded, OppPartTemp.SellCurrencyCode, OppPartTemp.ABPPrice, OppPartTemp.SellPrice, OppPartTemp.IsChosen, OppPartTemp.IsApplyDuty, OppPartTemp.IsFreight, OppPartTemp.IsCreditCard, OppPartTemp.Delivery, OppPartTemp.DeliveryUnits, OppPartTemp.DeliveryDays, OppPartTemp.SupplierNotes, OppPartTemp.RSChangeDate, OppPartTemp.RSArriveDate, OppPartTemp.RSSiteID
HAVING (((Count(OppPartTemp.StockPart_ID))>1));

As you can see there are many columns and this produces about 40465 duplicated records.

This happened because an import routine was run twice in error.

I have seen many solutions but they only seem to be able to handle one comparison colum.

If anybody could help, that would be great.

 

[LittleSmudge]

How about doing it in VBA in a step by step process. It will probobly take a long time - but run it overnight and it will be fine.
In addition the concet is easy and you are less likely to maake a mistake

Open a Recordset based on the entire table

Open a second Recordset WHERE Prime Key <> PrimeKey in RecordSet1 but all relevant matching fields are =

If Not rst2.EOF Then Delete the records in Recordset2

rst1.MoveNext


'ope-that-elps.


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[Craig0201]

shushu,

Are these duplicates in every field? If so.....

1) Run a make table query based on the query above
2) Delete all records in the master matching records in the newly made table
3) Insert the newly made table back into the master
4) Drop the newly made table
5) Compact the db.

Let's not clog the CPU with recordsets for a job that can be done in SQL.....

Craig

 

[dodge20]

Access has a really easy way of doing this. I think it is an append query. If you go to the help, and type delete duplicates, there is a step by step process that takes you through this. Dodge20