The purpose of this view example is to list the Event Participants for Events, including Participant Notes and the amount paid to date.

Difficulty: Advanced
Purpose: To quickly view the Event Participants for events with Participant Notes and the amount paid.
Page: Event Participants

This view shows the Event Participants for events, including Participation Status, Participant Notes, and amount paid compared to the full cost of the event.

Columns (View Field List)

This field shows the follow columns: 

Event_ID_Table.[Event_Start_Date] AS [Event Start Date]
, Participant_ID_Table_Contact_ID_Table.[Last_Name] AS [Last Name]
, Participant_ID_Table_Contact_ID_Table.[First_Name] AS [First Name]
, Event_ID_Table.[Event_Title] AS [Event Title] , Participation_Status_ID_Table.[Participation_Status]
  AS [Participation Status]
, Event_Participants.[Notes] AS [Participant Notes]
, (SELECT SUM(Line_Total) FROM Invoice_Detail ID 
   WHERE ID.Event_Participant_ID = Event_Participants.Event_Participant_ID) AS [Event_Cost]
, (SELECT SUM(Payment_Amount) FROM Payment_Detail PD INNER JOIN Invoice_Detail ID 
   ON ID.Invoice_Detail_ID = PD.Invoice_Detail_ID 
   WHERE ID.Event_Participant_ID = Event_Participants.Event_Participant_ID) AS [Amount_Paid]

Criteria (View Clause)

This criteria limits the results to only Event Participants where the Participation Status contains a value.

Participation_Status_ID_Table.[Participation_Status] IS NOT NULL

Techniques

This View uses the following SQL functions and commands:

• SUM
• INNER JOIN