displaying/searching data "two tables away"

[greenbox]

I feel that this should be something I can do, but I am really having a tough time! I've created a set of related tables...

STUDIOS<< MOVIES(acts as a join)>> ACTORS

So when I look at studios, I can see all the movies a particular studio has produced.

In addition, I would like to see, per studio, the number of films that a studio made that had a female actor in the lead role.

I am hoping that this is an easy problem, and I'll say "oh yeah RIGHT! now I get it" but I've kind of given up trying to solve it on my own.

[Ender]

There should be a Role table in there between Movie and Actor. In there you can indicate if the Role is a Lead, then relate to both the Movie ID and the Lead flag. See the attached ER Diagram.

With that structure in place, you should have two TOs (table occurences) of Role (Role and Role by Lead). To find Studios that have Leads that are Female, add another TO of Actor linked to Role by Lead (call it Actor by Lead). In a layout based on Studio, add search criteria "Female" in the Actor by Lead::Gender field.

[greenbox]

Thanks, yes, I will try this!! I think I was trying to do it without the additional table occurrences...I will give it a shot and check back.