Access Delete Query

[suarve]

Hi,

I'm just wondering if there is decent way of achieving the following in access. I need to delete records based on a select query, from a main table. I tried using a join and tried appending the results of the select query to a another table - "duplicates" with no luck.

In the past i appended "DELETE" onto the end of the duplicate records and then ran a delete query using the criteria "DELETE" - which is very messy imo. There must be a way I can achieve this in a single delete query.

Thanks in advance

 

[Andre]

You could try

DELETE FROM {table} 
WHERE {primarykey} IN (SELECT {primarykey} 
               FROM {table} 
               WHERE {criteria})

- that is how I would normally do it

 

[JIMA]

Hi,

This link http://www.databasedev.co.uk/delete_query.html might be of some use.

If you want to find duplicate rows on a table you can use the "Group By" clause restricting on rows having count(*)> 1

e.g

select employerid
from employer
group by employerid
having count(*) > 1;

That would identify all non unique duplicate employerids . Don't know if that works in Access but it's pretty standard SQL so I guess it should.

Hope that's of some help.

Jim