[AccessD] Isolating matching and related records

Hey Jim,

I sent a response to the SQL list.

Thanks,

Mark


>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: [AccessD] Isolating matching and related records
>Date: Thu, 05 Jul 2007 19:58:25 -0700
>
>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

_________________________________________________________________
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_pcmag_0507