You'll likely need two more tables, one with one record for each committee and one to list committee membership.
Personnel---<CommitteeMembership>----Committees ( ---< means 1 to many )
Personnel::PersonnelID = CommitteeMembership::PersonnelID
Committee::CommitteeID = CommitteeMembership::CommitteeID
To list committee membership for one, a selected group, or all committees, you'd put together a summary report based on the CommitteeMembership table but include fields from both Personnel and Committees to fill in all the needed info.
This is a typical many to many relationship as many people can be a member of many committees.
Here's a demo file of a many to many relationship. It relates contracts to companies but it's the same concept:
Could you help me understand how I download this demo. I had a problem with doing this before. I went to the area, clicked download now but I can't see where I can find it.
There's a blue download button. Click it. Wait the 20 seconds while the system tries to sell you an upgrade for the download link to appear. Then click the download now text to download the file.
I have the two additional tables. I have entered the first committee each member is in. Now, when I go to enter the second committee a staff member is in, it treats it like I am creating a new Personnel member rather than a new entry for existing personnel. Tying PersonnelID and CommitteeID together in the relationships I think is the problem. I want to be able to pick the person's name in the field for Name on the Membership entry form. what am I doing wrong?
The demo file shows how to handle this.
- The portal should be based on the Join table, CommitteeMembership
- The relationships allow creation of records via the relationship for the join table from both Personnel and Committees
- In the portal on the Personnel layout, add the field for CommitteeID and format it as a pop-up menu with a 2 column value list. Column 1 is the CommitteeID number from the committees table. Column 2 is the committee name from this same table. Hide column 1. The value list will show committee names, but enter committee ID numbers to link the selected committee to the current personnel record.
Thank you. I hadn't thought about doing it as a portal. That worked.
Always the hero!