Delete Duplicate Records Based on Criteria

[chadd]

I have a log/table that contains the results of a program we use to clean hard drives. I will call this WipeTable. I cross-reference this table with another table that contains all of the units processed each day. I will call the second table UnitTable. Ultimately, I check to ensure that all of the records in UnitTable indicated as "wiped," have a corresponding record of success in the WipeTable. The WipeTable contains multiple duplicates. For various reasons, the wiping of a unit may be initiated multiple times, which means that Hard Serial Numbers are repeated I would like to remove the duplicates from WipeTable before I cross-reference against the UnitTable. Identifying the duplicates is easy. What I would like to know is how I can automatically delete certain records based on criteria described below.



Identify duplicate hard drive serial numbers
Of the duplicates, delete the records that have a "0" in the success field if another record contains a "1" in that field.
If none of the duplicate records have a "1" in the success field, delete all except one record with "0"

WipeTable
Hard Drive Serial Number (alphanumeric)
Success of Wipe (1 = success, 0 = not a sucess)

UnitTable
Hard Drive Serial Number
Wipe Status

Any ideas?

[June7]

Review this article http://www.databasejournal.com/featu...ess-Tables.htm

[chadd]

Thank you June 7. I have been away for the last day or two and unable to attempt the solutions outlined in the article. Thank you for passing them along. At the minimum they will help me by providing a foundation that I will hopefully be able to tweak to suit my needs.