Access Deleting Duplicate Records Only When They meet Special Conditions
Posted on 2012-03-14
I'm running into what seems to be a simple problem but I can't figure it out and I'm done pulling my hair out.
I have a Table1 with two fields, Field1 & Field2. Field1 has some duplicate values in it. Field2 has only value 'X' and value 'Y'.
I need a simple way of deleting all the records which have a duplicate values in Field1 that have a field2 value of 'X'.
There is one more condtion, I only want to delete the records which have dulpicate values in Field1 but also have field2 values of both 'X' and 'Y'.
This is best explained by example. Below is an sample table. There are three duplicate values in Field1, '111' and '222' and '333'.
Record 1 would be deleted because it's a duplicate with a value of 'X' in field2 and record 5, the dulplicate has a value of 'Y'. I only want to delete the records with dups in field1 that have at least one record with a field2 value 'X' and at least one record with a field2 value of 'Y'.
In this example neither record 2 or 6 would be deleted since there is no records with a field1 value of '222' and a field2 value of 'X'. The same is true for Records 7 & 8 which are dups in field1 but none of the records have a field2 value of 'Y'.
Record Field1 Field2
1 111 X
2 222 Y
3 333 X
4 444 X
5 111 Y
6 222 Y
7 333 X
8 333 X
Guess that was not as easy as I thought it would be. 500 point to the winner!!! Thanks a bunch,