populating a field based on conditional values

[databaselobbyist]

I am new so please be patient.  I am trying to figure out how populate a filed based on a conditional value.  I may not be stating this correctly so I will explain.  I have created two databases one called "committees" and one called "committee membership". The committee table has the unique id filed "committeeid", "body", and "committee" fields.  the committee membership table has the same plus fields for "memberid" and "position".  I have created a conditional value list whereby I can choose the body and it will allow a drop down for the committee based on the body.  Is it possible to then populated the "committeeid" based on this information? 

the idea is if I have a member and I am trying to assign a committee to him/her I can do so with a filter and not by scrolling through the potential hundreds of committees, yet still have that member tied to a specific committee that other members can belong to as well.  

I hope this makes sense.

[Steve Martino]

Clear as mud.

Quote

I have created two databases one called "committees" and one called "committee membership".

Do you mean you have two 'Tables'?

You might be talking about a conditional value list.  When you pick 'Bob' in one field, the next field is a drop down field that only shows the committees that 'Bob' is assigned to.

If this is the end goal, then first we have to make sure the two tables are linked by a correct relationship.

Then we need to know how the committees are assigned, i.e. how does 'Bob' get the attribute of a committee, how to assign committees, etc.

If I'm on the right track I would have 3 tables (for starters):

Member---<CommitteMembership>---Committee

CommitteeMembership is a join table between Member and Committee.   There will be a primary key in Member, a primary key in Committee, and in the join table two foreign keys-on from Member and one from Committee.

Let me (us) know if this is where you're trying to go, and we'll provide more help

[Steve Martino]

I would also look at the Event Management Starter Solution for some ideas.