How to constrain values displaying in pick-list?

[keuler]

Experts-

Please assist this newbie with this pick list "constraint" problem.

BACKGROUND

Suppose I have the following tables to support a web application that allows members of an organization to sign up for activities. The table relationships are:

SIGNUP <-- MEMBER --> GROUP --> GROUP-ACTIVITY <-- ACTIVITY

(the arrows point to the "many" in one-to-many relationships.)

So, suppose we have:

1. 2 records in table GROUP: group_a and group_b.
2. 2 records in table ACTIVITY: hiking, swimming, and biking.
3. For group_a, there are two child record in GROUP-ACTIVITY, one for hiking, the other for swimming. group_b has has one record, for biking, in GROUP-ACTIVITY.
4. For simplicity, we have just 2 people in table MEMBER: member_a and group_leader_a
5. Both members belongs to group_a.
6. member_a has presently a record in the SIGNUP table for hiking.
7. The group_leader_a has rights to view the MEMBER records for group_a members and to sign them up/delete them from activities.

I'm envisioning that when group_leader_a goes to enroll member_a in additional activities, he selects member_a from a grid displaying all group_a members. At this point, a list of activities appears that member_a has signed up for, along with buttons for adding and deleting activities.

Note: The SIGNUP table should have at least these fields:

• signup_id
• activity_id
• member_id

THE PROBLEM

My immediate questions, in increasing difficulty, have to do with the how to create the list of selectable activities when group_leader_a clicks the Add button. Specifically:

1. How do I make the activity name (stored in the activity_name field in the ACTIVITY field) appear in the list when the SIGNUP table has just the activity_id value? (I'm sure this is an easy field lookup.)
2. How do I make the list of available activities for member_a NOT include those activities that he is already signed up for?
3. How do I make the list of activities additionally constrained to include only those activities member_a has the rights to sign up for by virtue of the his inclusion in group_a?

So, in our example, the displayed list of activities member_a can still sign up for would only show swiming: He's already signed up for hiking, and biking doesn't appear in the list because group_a members don't have the rights to sign up for it. (Group_b's members can sign up for biking, and only biking.)

THANKS IN ADVANCE FOR YOUR HELP!

-Kurt