I have 3 tables used in a reportMembers (Parent)
Events (Parent)
Activities (Child to both) (Events participated in by members)
I have a statistical report showing participation in the various events for year-to-date.
There are 5 targeted Event types of interest for this reportCallout, Interview, Staffing Net, Team Meeting, Team Net
I have a query which groups and counts each targeted event type and produces this output:Callout count 5
Interview count 1
Staffing Net count 3
Team Meeting count 5
Team Net count 20
The report is based on several queries, which do most of the busy work.
In the Report, I need to have all 5 events types show up for each individual, even when an individual may not have participated in any one of them. Example:
John Doe (except for the name, Except for the Callout line, this is an actual entry from the report. This post form will not maintain columns, so I have delimited with "|"s)Event | Attended | Events | %
Callout | 0 | 5 | 00.00
Interview | 1 | 1 | 100.00
Staffing Net | 1 | 3 | 33.33
Team Meeting | 1 | 5 | 20.00
Team Net | 5 | 20 | 25.00
Sub group Totals | 8 | 34 | 23.52
The current report does not include events one does not participate in, so, in this example, it does not include the event type, Callout, for John Doe. For him, I need it to show Callout with a 0 count of attendance, as shown, so I can get the correct average percentage of overall participation.
Does anyone have a suggestion how to do this?
If it helps, here are the queries which support the report, listed from the bottom up
Events for Year (runs first, obtains the year parameter and gathers the events for that year)SELECT Year([Beg_Date]) AS Expr1, Events.Beg_Date, Events.Event_Type, Events.ID
FROM Events
WHERE (((Year([Beg_Date]))=[Enter Year]) AND (Not (Events.Beg_Date)>Date()) AND ((Events.Event_Type)="Callout" Or (Events.Event_Type)="Interview" Or (Events.Event_Type)="Staffing Net" Or (Events.Event_Type)="Team Net" Or (Events.Event_Type)="Team Meeting"));
Count Events by TypeSELECT [Events for Year].Event_Type, Count([Events for Year].Event_Type) AS [CountOfEvent Type]
FROM [Events for Year]
GROUP BY [Events for Year].Event_Type;
Activities for Year (gathers the relavent Activities for that year)SELECT Activities.[Event ID], Activities.[Member ID], Activities.Activity, Activities.Beg_Date, Activities.Hours, [Events for Year].Expr1
FROM Activities, [Events for Year]
WHERE (((Activities.[Event ID])=[Events for Year].[ID]) AND ((Activities.Hours) Is Not Null));
Activities Unique (groups activities by member ID and Event ID)SELECT [Activities for Year].[Member ID], [Activities for Year].[Event ID], Max([Activities for Year].Activity) AS MAct, Max([Activities for Year].Beg_Date) AS MaxOfBeg_Date, Max([Activities for Year].Hours) AS MaxOfHours, [Events for Year].Expr1
FROM [Events for Year], [Activities for Year]
GROUP BY [Activities for Year].[Member ID], [Activities for Year].[Event ID], [Events for Year].Expr1;
Activities Query (Does most of the busy work for the report)SELECT DISTINCTROW [Activities Unique].[Member ID], [Activities Unique].MAct, Count(*) AS CountOfMaxOfActivities, Round([CountOfMaxOfActivities]/[CountOfEvent Type]*100,2) AS Expr2, [Count Events by Type].[CountOfEvent Type], [Count Events by Type].Event_Type, Max([Activities Unique].Expr1) AS MaxOfExpr11, [Members Active].Call, [Members Active].[1J], [Members Active].Mem_Name
FROM ([Activities Unique]
RIGHT JOIN [Members Active] ON [Activities Unique].[Member ID] = [Members Active].ID)
LEFT JOIN [Count Events by Type] ON [Activities Unique].MAct = [Count Events by Type].[Event_Type]
GROUP BY [Activities Unique].[Member ID], [Activities Unique].MAct, [Count Events by Type].[CountOfEvent Type], [Count Events by Type].Event_Type, [Members Active].Call, [Members Active].[1J], [Members Active].Mem_Name;
The report calls the Activities QueryGroups and sort by members, sorts the event types, calculates percentages, and group totals