Event Participants with Amount Paid
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 NULLTechniques
This View uses the following SQL functions and commands:
-
SUM
-
INNER JOIN