[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