[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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com