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))

Participants In Groups

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))