Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Deleting Duplicate Records Only When They meet Special Conditions

Posted on 2012-03-14
5
Medium Priority
?
260 Views
Last Modified: 2012-03-16
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
0
Comment
Question by:joshcallahan1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 37723497
Delete from yourtable t1
    where field2 = "X"
          and exists (select * from yourtable t2 where t1.field1=t2.field1 and t2.field2 = "Y")
0
 
LVL 40

Expert Comment

by:als315
ID: 37723507
Run QDelete from sample
DBdupl.mdb
0
 
LVL 15

Accepted Solution

by:
Simon Ball earned 1500 total points
ID: 37723799
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.
0
 

Author Closing Comment

by:joshcallahan1
ID: 37725885
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")))
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37728374
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 :)
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question