Removing Duplicate

[Janice7]

k, I got a table like the following:

CardNumber PaymentDate AmountDue ... ...
10001 12/4/2001 $5.00
10002 10/7/2001 $4.50
10006* 11/15/2001 $15.60
10006* 11/3/2001 $15.60
10007 10/8/2001 $1.00
10009* 1/29/2002 $4.00
10009* 1/15/2002 $48.70
10009* 5/12/2002 $78.30
10023 3/12/2002 $13.45
... ... ...

I want to delete BOTH (or more) records when a duplicate occurs with the CardNumber. In other words, all the records that I marked with a * needs to be deleted from the table.

What is the fastest way? If there is no technical solution, please let me know also. Any help would be appreciated.

Janice ;-)

 

[oharab]

This delete query will do it:

DELETE DISTINCTROW tblData.CardNumber
FROM tblData
WHERE (((tblData.CardNumber) In (SELECT DISTINCTROW tblData.CardNumber
FROM tblData
GROUP BY tblData.CardNumber
HAVING (((Count(tblData.CardNumber))>1))));

Basically it creates a sub query that finds duplicate card numbers and bases the main query on that.

HTH

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[Janice7]

Thank you very much. What if I want to leave the first record and remove the other duplicates? For example:

CardNumber PaymentDate AmountDue ... ...
10001 12/4/2001 $5.00
10002 10/7/2001 $4.50
10006 11/15/2001 $15.60
10006* 11/3/2001 $15.60
10007 10/8/2001 $1.00
10009 1/29/2002 $4.00
10009* 1/15/2002 $48.70
10009* 5/12/2002 $78.30
10023 3/12/2002 $13.45
... ...

the records marked with * needs to be removed.

Janice

 

[Janice7]

Thank you very much. What if I want to leave the first record and remove the other duplicates? For example:

CardNumber PaymentDate AmountDue ... ...
10001 12/4/2001 $5.00
10002 10/7/2001 $4.50
10006 11/15/2001 $15.60
10006* 11/3/2001 $15.60
10007 10/8/2001 $1.00
10009 1/29/2002 $4.00
10009* 1/15/2002 $48.70
10009* 5/12/2002 $78.30
10023 3/12/2002 $13.45
... ...

the records marked with * needs to be removed.

Janice