Deleting duplicate data 4

[Mr2006]

Hello,

I have a table that has many duplicate records. I need to be able to delete all the duplicates accept one from each group of duplicate. Do you have any suggestions?

Thanks

 

[HarleyQuinn]

Hmm, the way I'd probably do it would be to base a make table query (to make a new duplicate free table, you can rename to original later if correct) and then add all the fields from the first table and group by them all. That should eliminate all duplicate records.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

Have a look here:
faq701-5721

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BNPMike]

An easy way to do this is to use Access in-built functionality. Create a new table and apply a unique index to the keys where duplicates are occurring. You should have done this to start with so you might as well do it now. Now do Append queries and move all your data into the new table. Access will reject all the duplicates without you doing anything, and of course it will continue to so so from now on.

 

[Mr2006]

I am trying the different ways to do this. But the problem is that this is a data dump wilth 309000 record. each row has about 10 fields and the wole row is duplicate. so when you say "Create a new table and apply a unique index to the keys where duplicates are occurring", I am not sure which field will be the one to apply unique index to.

 

[lespaul]

Try:

INSERT INTO NewTable (SELECT DISTINCT * FROM OldTable)

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[BNPMike]

lespaul's solution will work but you should really decide what you key is. From a logical/business point of view what fields or combination of fields should identify one and only one record? If you can't work this out it might mean your table is not actually a relation. A relation must have a unique key even if it's all of the fields taken together. If your data is not relational, Access will still work but it won't work properly as SQL expects relations. One of the ways it won't work properly is it won't be able to reject duplicates so you potentially get back into the situation you are in at this point, so it's worth thinkingg this through. Maybe you could tell us the name of the table and the names of its fields. We may be able to guess what's going on.

 

[Mr2006]

Thank you all for the suggestion. I created a unique field using all the field in the table, then I created a new table with ID field that will not take duplicate. I appended the table to this new table, access rejected all the duplicates.

Thank you so much for all the suggestions.