AnsweredAssumed Answered

extracting information from a "join table"

Question asked by TerryCoolidge on Aug 2, 2011
Latest reply on Aug 2, 2011 by philmodjunk


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!