Count participation uniquely

[WCStarks]

I have a database with 3 table related to this issue
Members [one]
Activities [many]
Events [one]

The activities table links to both Members and Events
A member participates in an event via the Activities table.
A member can participate multiple in multiple shifts in the same event.
The stats report counts how many events for year to date
And how many activities a member participates in for that same period.


The stats report needs to show only that a member participated at least once in an event, and no more than once.
Otherwise, he will have a participation count greater will be greater than the number of events.
How do I get a query to count uniquely?
The current count in Design View is like:
Count of Activities: Count(*)
It need to do it for where the Event_ID is unique.

[orange]

??Where do shifts, periods fit?

How do I get a query to count uniquely?
Of shifts per Activity, shifts per Event, shifts per Period?
Shifts regardless of Activity or Event?

[WCStarks]

Events are of multiple types.
Each event may span 1 or more days, with multiple shifts per day.
A person may participate in one, or more shifts per day or over multiple days.
I calculate the number of events for each event type from year to date
I calculate the total number of events (Activities) he/she participated in by event type.
I calculate a % of participation in events for each event type.
I calculate the number of events by type participated in, divided by the total number of events by type. This should not exceed 100%.
Here is a sample of the report. You will notice some have more than 100% in some event types. I need to avoid this.
The activity column matches the Event type of the associated event records of that type.
The current expression is Count of Activities: Count(*), which counts all activity records of an event type. It needs to do this uniquely by Event ID, so it only counts once for each Event ID, regardless of how many times the person participated in a particular event.
Some may have participated in one event twice and not at all in another event, so he might show 3 out of 3, when he should only show 2 out of 3.

[Micron]

Is this a continuation of https://www.accessforums.net/showthread.php?t=70547 ?
Except now it is about counts rather than percentages? I don't see where the other thread was solved.

[orange]

??? You have to carefully define what you are measuring. You mention shifts and span, but your example shows activities such as

meetings, projects, interview, callout which do not indicate or relate to span or shift. I see only (may be my misinterpretation) a number of meetings and the number of those attended by the individual. I understand 3 meetings and Glen Meyer attended 2--so 2/3
= 66%.
However, I don't understand how Tyler Packer can attend 4 interviews of 1<----Whatever is being measured is unclear-- does not follow the same pattern as the Glen Meyer example.

What is your database structure --tables and relationships?

[WCStarks]

The meetings (Events), include multiple participants. An activity links a person to an event. In the case of call-outs, they usually consist of multiple 6 hour shifts through out the event. It indicates when and how long he participated in the event. In the case of a meeting, his activity date/times will usually equal that of the event. In the case of a call-out, the associated Activities indicate on which shift he part participated. If he participated on multiple shifts, he will have an Activity record for each shift.

The Interview you mentioned, spanned 3 days. Tyler attended once as an interviewee, and 3 times as an interviewer on the different days of the Interview event. So, he has 4 Activity records linked to the event record, but we only need to show he participated in the overall event. For the purposes of the stats, we don't want to count each of his separate activities at that event, just that he participated in the event. So I need to count an Activity for each unique event type.

Below is the relationship chart. I have disabled forced referential integrity on most of the links. to allow me some wiggle room, while I am developing the database.

[WCStarks]

It is. I had forgotten about the earlier thread. I have since worked out how to calculate the percentages, but I need to resolve this issue with counting Activities so I don't produce a percentage greater than 100%, even when they participate multiple times in the same event.

[WCStarks]

It is. I had forgotten about the earlier thread. I went and marked it solved. I have since worked out how to calculate the percentages, but I need to resolve this issue with counting Activities so I don't produce a percentage greater than 100%, even when they participate multiple times in the same event type.