I’m working a database maintaining a record of contacts and events in which they participate. I have a Contacts table with Email as primary key, an Events table with Event Name as primary key, and a Participation table with an auto primary key and foreign keys of email and event name. A contact can play one or more of 8 different roles in an event. A user can perform queries on Participation from a form created for that purpose. In addition to simple queries such as email and/or event, I need to provide 2 query types by role. The first query should return records where a contact played one or more of up to 3 roles specified in the query. The second query should return records where a contact played all the roles specified in the query (up to 3).
Originally, all the Roles played by a contact at an event were comma-separated in a text field (not my choice). The queries used “Like *Role*” in criteria for the text field with either OR (first query) or AND (second query). Role query fields on the query form were checked for NULL roles as well. It made for a very complex query and was not a very robust solution.
My question is, if each record in Participation were to have only one role, and a situation where a Contact played more than one role results in multiple records, how can I define the second query (AND)?