extracting information from a "join table"
PhilModJunk helped me out last week by explaining to me how to set up a "join table" so that I could establish a many-to-many relationship in my database. I have hundreds of members in one table and a few dozen committees in another, and I now am able to assign a member to various committees using a members-based layout, or I can populate a committee with members using a committees-based layout. The Committees_Assigments table has just two fields. MemberID and CommitteeID. Records in this table are created or deleted based on the activity in the other two tables (Members and Committees). I now need to understand how best to retrieve data from this "join table." For example, I want to be able to display a "Number of Committee Assignments" on a particular member's layout. I could then run a report showing numerically how many committee assignments each member has. Likewise, I'd like to be able to have a field on the committee layout that shows the number of members assigned to that committee along with the portal I have right now that shows all of the actual names. Being a novice, I just need to understand conceptually what the best approach is. A calculation field? A script? Some combination of the two? Wondering how to count the records in my join table based on found sets without being too convoluted. I have some ideas, but I doubt my initial instincts would be the most efficient or "proper" solution. Any suggestions would be greatly appreciated. Thank you!