delete duplicates

[Robbie]

I made a find duplicates query using the wizard, but now I need to delete
the duplicate records. I don't care which one is deleted so long as only one
exists. How can I turn this query into a delete query that will remove the
extra records?

SELECT Products.ISBN, Products.Title, Products.Binding, Products.ISBN13,
Products.ISBN10
FROM Products
WHERE (((Products.ISBN) In (SELECT [ISBN] FROM [Products] As Tmp GROUP BY
[ISBN] HAVING Count(*)>1 )))
ORDER BY Products.ISBN;


Thanks

 

[Jeff Boyce]

Robbie

One approach might be to first select unique values, then create a new table
that only has those.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Robbie]

So you are saying that I can't delete duplicates so I should rebuild my
table instead?

Robbie

One approach might be to first select unique values, then create a new
table that only has those.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I made a find duplicates query using the wizard, but now I need to delete
the duplicate records. I don't care which one is deleted so long as only
one exists. How can I turn this query into a delete query that will remove
the extra records?

SELECT Products.ISBN, Products.Title, Products.Binding, Products.ISBN13,
Products.ISBN10
FROM Products
WHERE (((Products.ISBN) In (SELECT [ISBN] FROM [Products] As Tmp GROUP BY
[ISBN] HAVING Count(*)>1 )))
ORDER BY Products.ISBN;


Thanks

 

[Cheese_whiz]

Hi Robbie,

Here's the basic technique.

http://support.microsoft.com/kb/209183

Two additional comments:

1. I've seen some people, apparently out of an (over?)abundance of caution,
suggest you make TWO copies of the original table (one a copy of just the
structure, and the other a copy of both structure/data) and, thereafter, work
just on the copies (until the end where you delete the original). I'm
paranoid, so I probably would do it that way, but I'd also backup the back
end before starting anyway.

2. The article doesn't say it, but I think you need to, after you've done
the appending and you are sure it's correct, go back into the new table (that
you appended to) and change the primary key to whatever the original table
used as a primary key. In other words, you changed the primary key so that
during the appending you would eliminate duplicates, but now it needs
changing back.

HTH,
CW

 

[Robbie]

I decided to just manually go through the duplicate query, and delete the
ISBN for the duplicate record... since I could sort alphabetically by the
product title, or numerically by the ISBN each item would appear twice in a
row. So I simply clicked in the first field hit delete arrowed twice and
repeated.

Then I went to the products table and deleted where ISBN was null

 

[Jeff Boyce]

Not saying "can't", offering an option.

I've had more luck (i.e., got done faster) by creating a new table and
filling it with the unique values, then renaming the table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

So you are saying that I can't delete duplicates so I should rebuild my
table instead?

Robbie

One approach might be to first select unique values, then create a new
table that only has those.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I made a find duplicates query using the wizard, but now I need to delete
the duplicate records. I don't care which one is deleted so long as only
one exists. How can I turn this query into a delete query that will
remove the extra records?

SELECT Products.ISBN, Products.Title, Products.Binding, Products.ISBN13,
Products.ISBN10
FROM Products
WHERE (((Products.ISBN) In (SELECT [ISBN] FROM [Products] As Tmp GROUP
BY [ISBN] HAVING Count(*)>1 )))
ORDER BY Products.ISBN;


Thanks