[AccessD] Isolating matching and related records

    You are most welcome Jim! The delete query suggested in my post was developed & tested successfully on Access 2003 desktop.

A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: Jim Lawrence 
  To: 'Access Developers discussion and problem solving' 
  Sent: Saturday, July 07, 2007 00:00
  Subject: Re: [AccessD] Isolating matching and related records


  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