The criteria utilizes EXISTS to check for matching records. The Contact's Participant_Record field is inserted to make this a correlated subquery. GETDATE and ISNULL are used to check that the current date is between the active date range for the Group Participant.
To create a View that lists all the Contacts who are active participants in a particular Group, the following SQL may be used for criteria.
EXISTS (SELECT 1 FROM Group_Participants GP
WHERE GP.Participant_ID = Contacts.Participant_Record
AND GP.Group_ID = 20
AND GETDATE() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date, GETDATE()+1))
The highlighted portion may be changed to a single Group ID or a list of Group ID like this:
IN (20, 359, 1002)
The highlighted portion may be changed to remove a single group:
NOT EXISTS (SELECT 1 FROM Group_Participants GP
WHERE GP.Participant_ID = Contacts.Participant_Record
AND GP.Group_ID = 20
AND GETDATE() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date, GETDATE()+1))
This View can easily be changed to work on the Participants page. The criteria looks like this:
EXISTS (SELECT 1 FROM Group_Participants GP
WHERE GP.Participant_ID = Participants.Participant_ID
AND GP.Group_ID = 20
AND GETDATE() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date, GETDATE()+1))