How to categorize names from checkbox value lists
Hi. I have a table for Contacts (called "Contacts"). One of its fields ("Profession") uses a value list depicting various business titles, like Manager, Producer, etc. One or two of these business titles would be applied to each person's record via check boxes.
I have another table for Artists who do business with the people in the Contacts table, such as who is the Artist's manager. In it are fields for "Manager", "Producer", etc. I want these fields in "Artists" to obtain their info via a drop-down list from the Contacts: Profession field.
The problem is that ALL the names from Contacts will show up in the drop downs. I want only the names corresponding to what checkbox had been checked. For example, if John Smith is a Manager ("Manager" checkboxed in his record in "Contacts"), I want his name to appear as a drop-down option in the "Manager" field in Artists, but not appear in the "Producer" field. I need to subdivide these contacts when in the Artists table.
What would be the script, calculation I would need for this (or what is the best way to do this)? Thank you.