How to find complete subsets within sets? Allen_N 14.08.2006 00:19:01 This has been bending my brain for days. I want to find all the kits that include all of the parts listed in a table called [Find items]. If I only wanted to select kits that contain any part in [Find items], the query would look like this: SELECT [Sub Kit Comps].KitName, [Sub Kit Comps].Item FROM [Sub Kit Comps] WHERE [Sub Kit Comps].Item IN (SELECT [Find Items In Kits].[Part Number] FROM [Find Items In Kits]) I've been trying to use subqueries to count the number of matches and compare that to the count of records in [Find Items In Kits], but I'm missing something. (I think I need to GROUP BY KitName, but I can't picture the correct syntax.) I've tried an SQL forum, and couldn't use the reply (it was just a restatement of my problem). Could I interest anyone in having a crack at it?

Re: How to find complete subsets within sets? strive4peace <strive4peace2006[ at ]yahoo.com> 14.08.2006 03:11:55 Hi Allen, where is Part Number for Kits? Is it in [Sub Kit Comps]? Oh, I see -- Item in [Sub Kit Comps] relates to [Part Number] in [Find Items In Kits]... it is best to keep fieldnames consisten when they represent the same information -- it is also a good idea to avoid spaces -- using underscore actually provides a better separation... instead of IN, how about NOT IN... and yes, you need to group by Kitname, but if you keep Item in your fields to show, this won't do anything because all items will show -- just depends on what you want... This should work, but it will be slow... you may want to check using NOT EXISTS ... http://www.techonthenet.com/sql/exists.php Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * Allen_N wrote: [Quoted Text] > This has been bending my brain for days. I want to find all the kits that > include all of the parts listed in a table called [Find items]. If I only > wanted to select kits that contain any part in [Find items], the query would > look like this: > > SELECT [Sub Kit Comps].KitName, [Sub Kit Comps].Item > FROM [Sub Kit Comps] > WHERE [Sub Kit Comps].Item > IN > (SELECT [Find Items In Kits].[Part Number] FROM [Find Items In Kits]) > > I've been trying to use subqueries to count the number of matches and > compare that to the count of records in [Find Items In Kits], but I'm missing > something. (I think I need to GROUP BY KitName, but I can't picture the > correct syntax.) > > I've tried an SQL forum, and couldn't use the reply (it was just a > restatement of my problem). Could I interest anyone in having a crack at it? >

Re: How to find complete subsets within sets? Allen_N 14.08.2006 03:41:01 Hi Crystal, Thanks for trying, but I'm still confused. The following SQL statement is something like what I'm trying to achieve, but it has a syntax error ("missing operator"): SELECT [Sub Kit Comps].KitName FROM [Sub Kit Comps] GROUP BY [Sub Kit Comps].KitName HAVING (COUNT (1) FROM [Find Items In Kits]) = (COUNT (1) FROM [Sub Kit Comps] WHERE [Sub Kit Comps].Item IN (SELECT [Find Items In Kits].[Part Number] FROM [Find Items In Kits])); "strive4peace" wrote: [Quoted Text] > Hi Allen, > > where is Part Number for Kits? Is it in [Sub Kit Comps]? Oh, I see -- > Item in [Sub Kit Comps] relates to [Part Number] in [Find Items In Kits]... > > it is best to keep fieldnames consisten when they represent the same > information -- it is also a good idea to avoid spaces -- using > underscore actually provides a better separation... > > instead of IN, how about NOT IN... and yes, you need to group by > Kitname, but if you keep Item in your fields to show, this won't do > anything because all items will show -- just depends on what you want... > > This should work, but it will be slow... you may want to check using NOT > EXISTS ... > > http://www.techonthenet.com/sql/exists.php > > > Warm Regards, > Crystal > * > (: have an awesome day :) > * > MVP Access > Remote Programming and Training > strive4peace2006 at yahoo.com > * > > > > Allen_N wrote: > > This has been bending my brain for days. I want to find all the kits that > > include all of the parts listed in a table called [Find items]. If I only > > wanted to select kits that contain any part in [Find items], the query would > > look like this: > > > > SELECT [Sub Kit Comps].KitName, [Sub Kit Comps].Item > > FROM [Sub Kit Comps] > > WHERE [Sub Kit Comps].Item > > IN > > (SELECT [Find Items In Kits].[Part Number] FROM [Find Items In Kits]) > > > > I've been trying to use subqueries to count the number of matches and > > compare that to the count of records in [Find Items In Kits], but I'm missing > > something. (I think I need to GROUP BY KitName, but I can't picture the > > correct syntax.) > > > > I've tried an SQL forum, and couldn't use the reply (it was just a > > restatement of my problem). Could I interest anyone in having a crack at it? > > >

RE: How to find complete subsets within sets? DomThePom 14.08.2006 17:09:53 Hi Allen This should do the trick for you: SELECT [Sub Kit Comps].KitName FROM [Sub Kit Comps] INNER JOIN [Find Items In Kits] ON [Sub Kit Comps].Item = [Find Items In Kits].[Part Number] GROUP BY [Sub Kit Comps].KitName HAVING (((Count([Sub Kit Comps].Item))=(SELECT Count([Find Items In Kits].[Part Number]) AS [CountOfPart Number] FROM [Find Items In Kits]))); "Allen_N" wrote: [Quoted Text] > This has been bending my brain for days. I want to find all the kits that > include all of the parts listed in a table called [Find items]. If I only > wanted to select kits that contain any part in [Find items], the query would > look like this: > > SELECT [Sub Kit Comps].KitName, [Sub Kit Comps].Item > FROM [Sub Kit Comps] > WHERE [Sub Kit Comps].Item > IN > (SELECT [Find Items In Kits].[Part Number] FROM [Find Items In Kits]) > > I've been trying to use subqueries to count the number of matches and > compare that to the count of records in [Find Items In Kits], but I'm missing > something. (I think I need to GROUP BY KitName, but I can't picture the > correct syntax.) > > I've tried an SQL forum, and couldn't use the reply (it was just a > restatement of my problem). Could I interest anyone in having a crack at it? >

RE: How to find complete subsets within sets? Allen_N 17.08.2006 06:01:01 Thanks a zillion, Dom. You're a bloody genius! "DomThePom" wrote: [Quoted Text] > Hi Allen > > This should do the trick for you: > > SELECT [Sub Kit Comps].KitName > FROM [Sub Kit Comps] INNER JOIN [Find Items In Kits] ON [Sub Kit Comps].Item > = [Find Items In Kits].[Part Number] > GROUP BY [Sub Kit Comps].KitName > HAVING (((Count([Sub Kit Comps].Item))=(SELECT Count([Find Items In > Kits].[Part Number]) AS [CountOfPart Number] FROM [Find Items In Kits]))); > > > "Allen_N" wrote: > > > This has been bending my brain for days. I want to find all the kits that > > include all of the parts listed in a table called [Find items]. If I only > > wanted to select kits that contain any part in [Find items], the query would > > look like this: > > > > SELECT [Sub Kit Comps].KitName, [Sub Kit Comps].Item > > FROM [Sub Kit Comps] > > WHERE [Sub Kit Comps].Item > > IN > > (SELECT [Find Items In Kits].[Part Number] FROM [Find Items In Kits]) > > > > I've been trying to use subqueries to count the number of matches and > > compare that to the count of records in [Find Items In Kits], but I'm missing > > something. (I think I need to GROUP BY KitName, but I can't picture the > > correct syntax.) > > > > I've tried an SQL forum, and couldn't use the reply (it was just a > > restatement of my problem). Could I interest anyone in having a crack at it? > >

Re: How to find complete subsets within sets? strive4peace <strive4peace2006[ at ]yahoo.com> 17.08.2006 06:51:07 I agree, Dom, brilliant! Thanks for posting a solution Warm Regards, Crystal * (: have an awesome day :) * MVP Access Remote Programming and Training strive4peace2006 at yahoo.com * Allen_N wrote: [Quoted Text] > Thanks a zillion, Dom. You're a bloody genius! > > "DomThePom" wrote: > >> Hi Allen >> >> This should do the trick for you: >> >> SELECT [Sub Kit Comps].KitName >> FROM [Sub Kit Comps] INNER JOIN [Find Items In Kits] ON [Sub Kit Comps].Item >> = [Find Items In Kits].[Part Number] >> GROUP BY [Sub Kit Comps].KitName >> HAVING (((Count([Sub Kit Comps].Item))=(SELECT Count([Find Items In >> Kits].[Part Number]) AS [CountOfPart Number] FROM [Find Items In Kits]))); >> >> >> "Allen_N" wrote: >> >>> This has been bending my brain for days. I want to find all the kits that >>> include all of the parts listed in a table called [Find items]. If I only >>> wanted to select kits that contain any part in [Find items], the query would >>> look like this: >>> >>> SELECT [Sub Kit Comps].KitName, [Sub Kit Comps].Item >>> FROM [Sub Kit Comps] >>> WHERE [Sub Kit Comps].Item >>> IN >>> (SELECT [Find Items In Kits].[Part Number] FROM [Find Items In Kits]) >>> >>> I've been trying to use subqueries to count the number of matches and >>> compare that to the count of records in [Find Items In Kits], but I'm missing >>> something. (I think I need to GROUP BY KitName, but I can't picture the >>> correct syntax.) >>> >>> I've tried an SQL forum, and couldn't use the reply (it was just a >>> restatement of my problem). Could I interest anyone in having a crack at it? >>>