This View example displays the Group Participants in a specific Ministry and the notes field of the Allergies and Special Needs field.

Purpose: To create a View that lists all the Group Participants from a Ministry and whether they have an Allergy or Special Needs.

Field List

Replace highlighted value with appropriate Allergy and Special Need ID:

Participant_ID_Table_Contact_ID_Table.[Nickname] AS [Nickname] 
, Participant_ID_Table_Contact_ID_Table.[Last_Name] AS [Last Name] 
, Group_ID_Table.[Group_Name] AS [Group Name] 
, Group_Role_ID_Table.[Role_Title] AS [Role Title] 
, Group_ID_Table_Ministry_ID_Table.[Ministry_Name] AS [Ministry Name] 
, Group_ID_Table_Congregation_ID_Table.[Congregation_Name] AS [Congregation Name] 
,(SELECT TOP 1 CA.Notes FROM Contact_Attributes CA JOIN Attributes A ON CA.Attribute_ID = A.Attribute_ID 
AND Attribute_Type_ID = 1 WHERE CA.Contact_ID = Participant_ID_Table.Contact_ID) AS [Allergy & Special Need]

View Clause

Replace highlighted value with appropriate Ministry ID:

Group_ID_Table_Ministry_ID_Table.[Ministry_ID] = 2   
AND GetDate() BETWEEN Group_Participants.Start_Date 
AND ISNULL(Group_Participants.End_Date,GetDate())

Variations

View All Members of a specific Group rather than a Ministry

View Clause (Replace highlighted value with appropriate role type ID):

Group_ID_Table.[Group_ID] = 1 
AND GetDate() BETWEEN Group_Participants.Start_Date 
AND ISNULL(Group_Participants.End_Date,GetDate())

• SELECT TOP