Auto deletion of duplicates

  • Thread starter Thread starter mrwhitescotland
  • Start date Start date
M

mrwhitescotland

Hi there.

I have two tables and want to delete duplicates.

ie if they appear in table A and table B then I want BOTH entries to be
deleted and be left with only those who only appear in either A or B.

Have absolutely no experience of SQL and basic access, so any help would be
gratefully received.

Thanks
 
How do you identify that a record in A is a duplicate of a record in B?

If you can do so using just one field then

DELETE
FROM A
WHERE A.IdentifierField IN
(SELECT B.IdentifierField
FROM B)

DELETE
FROM B
WHERE B.IdentifierField IN
(SELECT A.IdentifierField
FROM A)

Warning this permanently deletes the records. If you want to return a
record set that shows the two set of records that can be done.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
if you want AUTO delete you want a trigger and access dosnt support
triggers so you have to do it manually every time you want to clean
the data up

Regards
Kelvan
 
I was a bit unclear in my post. The SQL I posted would have to be two
separate queries in Access.

AND if you were just trying to return the result without deleting
records then you could use a UNION query.

SELECT A.*
FROM A LEFT JOIN B
ON A.IdentifierField = B.IdentifierField
WHERE B.IdentifierField is Null

UNION ALL

SELECT B.*
FROM A RIGHT JOIN B
ON A.IdentifierField = B.IdentifierField
WHERE A.IdentifierField is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Hi John,

I have run a test on two sample tables and I have managed to get the result
I was looking for.

Thanks a lot for the quick response and the excellent advice, it has made a
huge difference.

Cheers

Ed
 
Back
Top