Question Find common values in a many to many table (1 Viewer)

[Alisa]

I haven't run into this particular challenge before, maybe some of you have?

This is the db structure:

Entity A
Fields: Entity A

Entity B
Fields: Entity B

Associative Table
Fields: Entity A, Entity B, True/False

or with sample data:

Entity A
Entity A:
Cat
Frog

Entity B
Entity B:
Ears
Fur

Associative Table
Entity A: Entity B: True/False:
Cat Ears True
Cat Fur True
Frog Ears True
Frog Fur False

I want a query that returns Ears because Ears is true for Cat AND Frog.
The query should NOT return Fur because Fur is only true for Cat, it is NOT true for Frog.

It was simple enough to write a query that finds B = true for ANY value of A:

SELECT EntityB FROM AssociativeTable INNER JOIN EntityA ON AssociativeTable.EntityA=EntityA.EntityA WHERE TrueFalse=True;"

In my sample, this returns Ears AND Fur - NOT the result I want.
I can't figure out how to write it so that it only finds the ones that are common to ALL values of A.

My current solution is to loop through two recordsets and test each value individually to manually build a list of common values. This is a really big pain, and I think it will also get slow if the list gets long. Surely there is a logical way to accomplish this in a query, right?

 

[Fifty2One]

If you set the ears fur eyes whatever to a true/false then you could filter the list with the grouping turned on to look for min valve equals 0 and discount that field.
I do not understand why the attributes for the cat or frog is in a different table though...

 

[Alisa]

If you set the ears fur eyes whatever to a true/false then you could filter the list with the grouping turned on to look for min valve equals 0 and discount that field.
I do not understand why the attributes for the cat or frog is in a different table though...

Well yes, but that still returns fur and ears instead of just ears because fur = true for cat (unless I am misunderstanding your suggestion).

Obviously, this is not the real data, I was just trying to make a really simple example to show the concept. In reality, the true/false field is not indicating that the cat has ears, as I implied in my example, it is indicating that the relationship between cat and ears is approved.

 

[music_al]

why not have a table ANIMALS and have fields which represent their attributes, like...

tbl_Animal
Animal_Name
Has_Fur (Boolean)
Has_Ears
Has_Tail etc

It would be easy to build the query you describe from this. Or will this not be sufficient for your application. If you have separate table for animal and a separate table for attributes, at some stage you have to have a means of mapping the attributes to the animal. Where are you doing this ?

Al

 

[Alisa]

why not have a table ANIMALS and have fields which represent their attributes, like...

tbl_Animal
Animal_Name
Has_Fur (Boolean)
Has_Ears
Has_Tail etc

It would be easy to build the query you describe from this. Or will this not be sufficient for your application. If you have separate table for animal and a separate table for attributes, at some stage you have to have a means of mapping the attributes to the animal. Where are you doing this ?

Al

Actually its not like that at all - let me try a better example

Entity A
Entity A:
Widget 1
Widget 2
Widget 3

Entity B
Entity B:
Factory 1
Factory 2
Factory 3

Associative Table
Entity A: Entity B: True/False:
Widget1 Factory1 True
Widget2 Factory1 True
Widget3 Factory1 True

Widget1 Factory2 True
Widget2 Factory2 False
Widget3 Factory2 True

Widget2 Factory3 True
Widget3 Factory3 True

In this case, I want to return Factory 1, because that is the only factory that exists AND Is True for every widget. I don't want to return factory 2 because it is not true for every value of A. I don't want to return factory 3 because it doesn't exist for every value of A. Hopefully this is a little clearer than my first example.

 

[Fifty2One]

I guess I do not understand the whole structure of what you want to do because I still do not understand why you would not put it all into one table even with a different sample.
tblProducts
ThingName
Factory1 (boolean)
Factory2 (boolean)
Factory3 (boolean)
etc
Then just test for 3 (or how many factories) true by looking for a 0 with min or by nested IIF statements.

 

[Alisa]

I guess I do not understand the whole structure of what you want to do because I still do not understand why you would not put it all into one table even with a different sample.
tblProducts
ThingName
Factory1 (boolean)
Factory2 (boolean)
Factory3 (boolean)
etc
Then just test for 3 (or how many factories) true by looking for a 0 with min or by nested IIF statements.

Because that would be repeating groups = bad db design

However, I just found a solution. Its funny how just describing a problem to someone else can make you think of something new. I created a crosstab query where the value = first of true/false, the row heading is entity B, and the column heading is entity A. Then I added row totals. Since the true/false field has a value of -1 if the value is true, then if the value is true for every entity A, the absolute value of the row total is equal to the count of entity A. So I can just Select EntityB From Crosstab query WHERE Total= (count of EntityA). Thanks for listening!