Deleting Duplicates in Access 2000

P

Petep

Hi,

Anyone tell me what is the problem with this query? I am trying to
delete duplicates out of a large table without re-creating the table.
When i run the inside "SELECT (MIN" subquery, it runs super quickly and
returns a table with the 200-300 records that would be duplicates as
I'd hoped. But when i attach the outer DELETE statement to the query,
Access suggests that it is processing the query, but even after 2 hours
it seems to have made no progresss (according to the status bar). The
whole application appears locked up and only a Force-Quit can bring it
back from the dead.

Any Thoughts?

DELETE * FROM tblServices WHERE
Record_ID In
(SELECT MIN(Record_ID)
FROM tblServices
GROUP BY DAT_Code, Service_No
HAVING COUNT (Service_No) > 1)
 
V

Van T. Dinh

I am not sure but when I use the same Table in the MainQuery and the
SubQuery, I tend to use Aliases to distinguish between different copies of
the Table.

Try something like:

DELETE Main.*
FROM tblServices AS Main
WHERE Main.[Record_ID] In
( SELECT MIN(Sub.[Record_ID])
FROM tblServices AS Sub
GROUP BY Sub.DAT_Code, Sub.Service_No
HAVING COUNT (Sub.Service_No) > 1
)

Another possiblity is to create a set of 2 Queries, the first one is you
present SubQuery and the second one use the Table and the first Query as
datasources with inner join.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top