Access Deleting Duplicate Records Only When They meet Special Conditions

Hi,

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,

Josh
joshcallahan1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
Delete from yourtable t1
    where field2 = "X"
          and exists (select * from yourtable t2 where t1.field1=t2.field1 and t2.field2 = "Y")
als315Commented:
Run QDelete from sample
DBdupl.mdb
Simon BallChief information OfficerCommented:
i'd have a query with the table in twice, inner join on field1,  where tablename.field2 = x and tablename_1.field2 = y

run query and check its showing correct rows, then turn it into a delete query to delete records as required.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joshcallahan1Author Commented:
Exactly correct and the cleanest way possible.  Awsome!!  FYI below is the code for the example table:

DELETE *
FROM Table1 where ID in (select Xs.ID from Table1 AS Xs INNER JOIN Table1 AS Ys ON Xs.Field1 = Ys.Field1
WHERE (((Xs.Field2)="X") AND ((Ys.Field2)="Y")))
Simon BallChief information OfficerCommented:
nice one.

there might be a way to do the delete straight from the xs alias, withouth needing to have in(select XS.id...),

but i might be thinking of SQL server Tsql not Jet SQL.

either way it works, so well done :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.