[AccessD] Selecting Parents that have ALL Children

How about a subquery returning the left hand items for each of the list 
items in the list (5 items, 5 subqueries), all subqueries equal joined on 
the left hand column.  It would be easiest to generate a sql statement in 
code, looping through the items selected collection, adding a subquery for 
each item in the list.

Hen


>From: "Developer" <Developer at ultradnt.com>
>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] Selecting Parents that have ALL Children
>Date: Mon, 20 Oct 2003 13:44:14 -0400
>
>I am trying to construct a result set (proto-typing in A2k but want to
>have a SQL2000 sp at the end, an answer for either is ok), from a user
>form, where the user selects items from a list box.  These items are the
>possible "right side" of a many-to-many.  I loop the list box, and
>construct an IN clause, which works great to find a left-sider that had
>ANY of the children, but my users need the result set to be "who had ALL
>of these" (sort of a combined IN and AND).
>
>Tia for any assistance,
>Steve
>
>For example, if there are classes in Word, Access, Excel, and the user
>picked Access and Excel, which students took both, with tblStudent->
>tblStudentClass <-tblClass.  The tblStudentClass record with both id's
>just needs to exist, there is other criteria.
>
>
>
>
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*.  
http://join.msn.com/?page=features/featuredemail