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
'====================================================


mrwhitescotland wrote:
> 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

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

Thanks John,

I will give it a try and see what happens.

Cheers

Ed

"John Spencer" wrote:

> 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
> '====================================================
>
>
> mrwhitescotland wrote:
> > 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
>

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
'====================================================


mrwhitescotland wrote:
> Thanks John,
>
> I will give it a try and see what happens.
>
> Cheers
>
> Ed
>
> "John Spencer" wrote:
>
>> 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
>> '====================================================
>>
>>
>> mrwhitescotland wrote:
>>> 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

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

"John Spencer" wrote:

> 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
> '====================================================
>
>
> mrwhitescotland wrote:
> > Thanks John,
> >
> > I will give it a try and see what happens.
> >
> > Cheers
> >
> > Ed
> >
> > "John Spencer" wrote:
> >
> >> 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
> >> '====================================================
> >>
> >>
> >> mrwhitescotland wrote:
> >>> 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
>