[AccessD] Isolating matching and related records

Jim,

I went back and read my post since I didn't see a reference.  Apparently I 
didn't include the SQL.  Would a single delete statement with a subquery 
accomplish this?

************SQL**************
DELETE tblTest.Section, tblTest.code
FROM tblTest
WHERE (((tblTest.Section)="C1") AND ((tblTest.code) Not In (SELECT 
tblTest1.code
FROM tblTest AS tblTest1
GROUP BY tblTest1.Section, tblTest1.code
HAVING (((tblTest1.Section)="B1"));)));
************SQL**************


>From: Jim Lawrence <accessd at shaw.ca>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem 
>solving'"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Isolating matching and related records
>Date: Fri, 06 Jul 2007 11:30:21 -0700
>
>Hi A.D. Tejpal:
>
>Your code looks very good but some adjustments would have to be made to 
>make
>it work in my particular context. I worked backwards, first in MS SQL where
>SubQueries are available and then translated the result into Access's
>compound query system. Below is the result for the record:
>
>First one record of each match group is created in queries 1 and 2. Then 
>any
>missing matches are isolated in query 3 and query 4 deletes those matches. 
>I
>am sure this code could be greatly refined but the results were needed 
>fast.
>
>query1:
>SELECT DISTINCT tblRosterReportTemplate.SectionCode,
>tblRosterReportTemplate.JobCode
>FROM tblRosterReportTemplate
>WHERE (tblRosterReportTemplate.SectionCode="C1")
>
>query2:
>SELECT DISTINCT tblRosterReportTemplate.SectionCode,
>tblRosterReportTemplate.JobCode
>FROM tblRosterReportTemplate
>WHERE (tblRosterReportTemplate.SectionCode="B1")
>
>query3:
>SELECT query1.SectionCode, query1.JobCode
>FROM query1 LEFT JOIN query2
>ON query1.JobCode = query2.JobCode
>WHERE query2.SectionCode Is Null;
>
>query4:
>DELETE * FROM tblRosterReportTemplate
>WHERE SectionCode = query3.SectionCode'
>AND JobCode = query3.JobCode;
>
>Thank you so much for your help
>
>Jim
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
>Sent: Friday, July 06, 2007 12:51 AM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Isolating matching and related records
>
>Jim,
>
>     Delete query as given below, should get you the desired results.
>
>Best wishes,
>A.D.Tejpal
>---------------
>
>========================================
>DELETE *
>FROM T_Codes
>WHERE IIf([Section]="C1",IIf([Code] In (SELECT Code  FROM T_Codes As T1
>WHERE T1.Section = "B1"),0,1),0) > 0;
>========================================
>
>   ----- Original Message -----
>   From: Jim Lawrence
>   To: 'Access Developers discussion and problem solving'
>   Sent: Friday, July 06, 2007 08:28
>   Subject: [AccessD] Isolating matching and related records
>
>
>   Hi All:
>
>   The following question seems like a simple SQL question but I just can 
>not
>find the appropriate answer at this minute... too many long nights.
>
>   Given:
>
>   1. A table of many records, with each record having a couple fields 
>called
>{section] and [code].
>
>   2. The content of field [section] has only three possible entries of
>   "A1","B1" and "C1". The contents of field [code] can be any of a variety
>of codes.
>
>   3. There will always be records for group "A1" and "C1" but not always 
>for
>"B1"
>
>   What SQL code would find the group where there are no matching "B1"
>records and delete all the records in the "C1" group
>
>   An example of data could be;
>
>   5 records of [section] = "A1", [code] = "TTR"
>   4 records of [section] = "B1", [code] = "TTR"
>   30 records of [section] = "C1", [code] = "TTR"
>   3 records of [section] = "A1", [code] = "CYR"
>   47 records of [section] = "C1", [code] = "CYR"
>   5 records of [section] = "A1", [code] = "PIJ"
>   4 records of [section] = "B1", [code] = "PIJ"
>   30 records of [section] = "C1", [code] = "PIJ"
>
>   After the SQL code was run all 47 records with [section] = "C1' and 
>[code]
>= "CYR" would be gone.
>
>   It does not seem like a difficult process but...
>
>   MTIA
>
>   Jim
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
http://liveearth.msn.com