What would you like to know more about?

Group Participant Attributes

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

Difficulty: Advanced

Page: Group Participants

Features: Adds a field to show whether a Group Participant has a specific Attribute.

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

Techniques

  • SELECT TOP