Extending searches across related tables

[mbsteed]

I have a database of committee members - the purpose is to help determine who should be on committees.

To make a long story short I have a table for committee information (e.g. eligibility rules) and another table for all potential members with their status and a table linking Members to Committees. The relationships work fine to create a list of current committee members and previous committee members. However, when I go to create a list of potential committee members the relationships don't provide enough information because some members are not yet in the table that links committees to members. I want to use the rules in the committee table to search for eligible members but also use historical membership data from the linking table to delimit the eligible members further, as well as use the membership status from the membership table. In other words I need to combine information from 3 different tables through a search. I was able to be get a partial list using a loop to check the rule of membership status and put that into a value list for a pop-up menu. However, I need to determine if those folks satisfy other rules that are in other tables. Visually the logic could be represented in a venn diagram with overlapping components from the three tables - see the diagram below. I sense this is a common problem but t I have hit a brick wall. I have also posted an image of the table arrangement to help give you a sense for the context.

What would be the best way to do this?

Edited December 6, 2007 by Guest