Too many duplicate entries are being deleted

[Smigidy]

I'm trying to delete duplicate entries from two different tables, but if
there are two occurences on a table, both get deleted. How do I delete only
one corresponding record? For instance, I want to ignore(delete) receipt A
from table 1 and 2 since they match, but leave the second receipt since
there's no corresponding match.

Table 1 2
Receipt A A
Amount $1000.00 $1000.00
A
$1000.00

This is the code I have:

SELECT [1].[Receipt], [1].Amount
FROM [1] LEFT JOIN [2] ON ([1].[Receipt] = [2].Receipt) AND ([1].Amount =
[2].Amount)
WHERE ((([2].Amount) Is Null));

Thanks again,
Michael

 

[Doctor]

Allen Browne shows you how to do this using a Subquery. take a look at the
link below and he should answer your question.

http://www.allenbrowne.com/subquery-01.html#DeDuplicate

Doc B

 

[Smigidy]

I can't seem to get those to work, I keep getting errors in the FROM clause.
From what I read it's trying to get rid of duplicates in a table. If I then
bounce what's left with another one, everything will then be gone.

Allen Browne shows you how to do this using a Subquery. take a look at the
link below and he should answer your question.

http://www.allenbrowne.com/subquery-01.html#DeDuplicate

Doc B

I'm trying to delete duplicate entries from two different tables, but if
there are two occurences on a table, both get deleted. How do I delete only
one corresponding record? For instance, I want to ignore(delete) receipt A
from table 1 and 2 since they match, but leave the second receipt since
there's no corresponding match.

Table 1 2
Receipt A A
Amount $1000.00 $1000.00
A
$1000.00

This is the code I have:

SELECT [1].[Receipt], [1].Amount
FROM [1] LEFT JOIN [2] ON ([1].[Receipt] = [2].Receipt) AND ([1].Amount =
[2].Amount)
WHERE ((([2].Amount) Is Null));

Thanks again,
Michael

 

[Doctor]

I'm sorry, I didn't realize that the link took you to the top of the page.
The SQL example you need is most of the way down the page.

DELETE FROM Table1
WHERE ID <> (SELECT Min(ID) AS MinOfID FROM Table1 AS Dupe
WHERE (Dupe.Surname = Table1.Surname)
AND (Dupe.FirstName = Table1.FirstName));

The table named Table1 and the Table named Dupe are the same table. Replace
Surname and Firstname with whatever you want your duplicate records checked
by. If you follow the syntax, you could add several more or only have one
criteria. This query deletes all duplicates except the first one. If you
still have trouble, post your SQL, and I'll help you work through it.

I can't seem to get those to work, I keep getting errors in the FROM clause.
From what I read it's trying to get rid of duplicates in a table. If I then
bounce what's left with another one, everything will then be gone.

Allen Browne shows you how to do this using a Subquery. take a look at the
link below and he should answer your question.

http://www.allenbrowne.com/subquery-01.html#DeDuplicate

Doc B

I'm trying to delete duplicate entries from two different tables, but if
there are two occurences on a table, both get deleted. How do I delete only
one corresponding record? For instance, I want to ignore(delete) receipt A
from table 1 and 2 since they match, but leave the second receipt since
there's no corresponding match.

Table 1 2
Receipt A A
Amount $1000.00 $1000.00
A
$1000.00

This is the code I have:

SELECT [1].[Receipt], [1].Amount
FROM [1] LEFT JOIN [2] ON ([1].[Receipt] = [2].Receipt) AND ([1].Amount =
[2].Amount)
WHERE ((([2].Amount) Is Null));

Thanks again,
Michael