Finding parent records that have only specific child record values 2

[swaybright]

My actual application is a bit more complex, but this example demonstrates what I want to accomplish. I have 5 tables:

JellyBeans

JellyBeanID
Color​

Cups

CupID
CupName​

CupDetails

CupDetailsID
CupID
JellyBeanID
NumberofBeans​

Bowls

BowlID
BowlName
SurveyScore​

BowlDetails

BowlDetailsID
BowlID
CupID
NumberofCups
​

So basically I can create an inventory of cups that have some combination of jellybeans of specific colors, then I can use that inventory of cups to create bowls of jellybeans to make further combinations of colors. For any given bowl of jellybeans, I can trace the exact number of each color.
What I would like to do is select the bowls that have only a specified dynamic combination of colors. For example select all bowls that have only red, white, and blue jellybeans.
Note also that new colors can be added at anytime, so I would like to maintain this flexibility without rewriting the query every time a new color is added to the JellyBeans table.

Thanks!
Shane

 

[Andrzejek]

If your [tt]JellyBeans[/tt] table looks like this

[pre]
JellyBeanID Color
1 red
2 white
3 blue
4 black
[/pre]
you may in your Select statement have something like:[tt]
Select ... From ...
Where JellyBeans.JellyBeanID IN (1, 2, 3)
And ...
[/tt]
As far as "flexibility without rewriting the query". Display to the User set of check boxes created 'on-the-fly' based on what's in your JellyBeans table

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[swaybright]

Thanks Andy!
That almost does what I want and it helps me get moving in the right direction. However, it returns all bowls that have red, white or blue regardless of the other colors that are present. I would like it to exclude bowls that would (in your example) contain black so that the resulting dynaset would be bowls that have red, white, blue, red/white, red/blue, blue/white, and red/white/blue; but none contain black even if they contain red, white, and/or blue.

I get the feeling that I need to create a form that has 3 list boxes: a master list of jellybean colors, an IN box, and a NOT IN box with buttons like in the form wizard to move items back and forth. I saw something like that somewhere--anybody remember?

Thanks!

 

[dhookom]

Assuming the Northwind database with a CustomerID (cups) in the Orders table and ProductID (colors) in the Order Details table.

I would first create a query that returns the unique Products by Customer (cups and colors):

qgrpCustProds

SELECT Orders.CustomerID, [Order Details].ProductID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.CustomerID, [Order Details].ProductID;

Then to find the customers who had ordered products 10,29,72,75 and no other products, the query would be:

SELECT qgrpCustProds.CustomerID
FROM qgrpCustProds
WHERE qgrpCustProds.ProductID In ([b][highlight #FCE94F]10,29,72,75[/highlight][/b]) AND 
(SELECT COUNT(*) from qgrpCustProds CP where cp.CustomerID = qgrpCustProds.CustomerID))=[b][highlight #FCE94F]4[/highlight][/b]
GROUP BY qgrpCustProds.CustomerID;

Duane
Hook'D on Access
MS Access MVP

 

[swaybright]

So I created a subquery with the same syntax as above except with

Where JellyBeans.JellyBeanID NOT IN (4) as the criteria for BowlID

So something like this:
Select BowlID
From Bowls...(all the join statements)
Where JellyBeans.JellyBeanID IN (1, 2, 3)
And
Bowls.BowlsID Not In (

Select Bowl ID
From Bowls...(all the join statements)
Where JellyBeans.JellyBeanID NOT IN (4)
)
​

 

[swaybright]

Thanks Duane!
I think I am on the right track to solve the problem. I found the FAQ for the listboxes.

http://www.tek-tips.com/faqs.cfm?fid=6326

I ought to be able to slide the items between the IN and Not IN boxes and have super flexibility to build the query to not only as I described but also with the ability to exclude certain colors and be indifferent about others.

My thanks to you and Andy for the great advice!