have a report show a fixed set of event types in a report

[WCStarks]

I have 3 tables used in a report

Members (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 report

Callout, 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 Type

SELECT [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 Query

Groups and sort by members, sorts the event types, calculates percentages, and group totals

[AccessToGo]

Hi, How about this...

Create a new query (Virtual Activities) which selects all Events and all Members (no join between the tables) and select EventID and MemberID in the grid. This will return all possible combinations of activity. You might want to restrict members and events in some way if they just aren't pertinent.

Now in another new query bring in the new Virtual Activies query and the Actvity table. Join them on MemberID and EventID with (I think) a left join so that all Virtual Activities are included. You can then use this result to build the rest of your report.

Will that give you what you need?

Regards
Chris

[WCStarks]

Thank you for your reply.

All members are included with or without activities
Query Events for Year gathers all the filtered Events relevant to this report
Query Count Event Types produces a unique list of events with count by event type
Query Activities for Year produces a set of all filtered relevant Activities
Query Activities Unique brings Event for Year and Activities for Year.

I'm thinking I can use Query Events for Year as the Events table and query Activities for Year as the virtual events and activities tables.

I'll experiment and see if I understand correctly what you are suggesting. What do you mean by "... in the Grid"?

[AccessToGo]

Hi, in the grid = I was referring to the regular query design (top panel tables area and bottom panel grid of columns selected, parameters etc.)