INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Finding parent records that have only specific child record values

Finding parent records that have only specific child record values

(OP)
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

RE: Finding parent records that have only specific child record values

If your JellyBeans table looks like this

JellyBeanID   Color
1             red
2             white
3             blue
4             black
 
you may in your Select statement have something like:
Select ... From ...
Where JellyBeans.JellyBeanID IN (1, 2, 3)
And ...

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.

RE: Finding parent records that have only specific child record values

(OP)
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!

RE: Finding parent records that have only specific child record values

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

CODE --> SQL

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:

CODE --> SQL

SELECT qgrpCustProds.CustomerID
FROM qgrpCustProds
WHERE qgrpCustProds.ProductID In (10,29,72,75) AND 
(SELECT COUNT(*) from qgrpCustProds CP where cp.CustomerID = qgrpCustProds.CustomerID))=4
GROUP BY qgrpCustProds.CustomerID; 

Duane
Hook'D on Access
MS Access MVP

RE: Finding parent records that have only specific child record values

(OP)
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)
)

RE: Finding parent records that have only specific child record values

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close