How to find complete subsets within sets?

[Allen_N]

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?

 

[strive4peace]

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]

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]));

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
*

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?

 

[DomThePom]

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]

Thanks a zillion, Dom. You're a bloody genius!

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])));

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?

 

[strive4peace]

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
*

Thanks a zillion, Dom. You're a bloody genius!

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])));

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?