Counting the number of distinct dates in a report column

[Ptla]

I am the Treasurer for a small church. I have been asked to create a database to track member attendance at different events such as (Sunday Worship, Bible Class, Communion, Etc.). I am using Access 2013. I have created 3 tables, they are Member, Event, and Events Tracker. Events Tracker has 3 fields: Member, Event, and Event Date. The Member and Event fields use drop down boxes from their perspective tables. I have created a report that is grouped on the Event Date. When I filter a specific event how can I have the Event Date column only a number that gives the total of specific dates rather than a count of all the records? Then how can I take the total of the records and divide by the number of distinct dates?



11/5/2023
11/10/2023
11/5/2023
11/15/2023
11/20/2023
11/5/2023

In this example there are 6 records with 4 distinct dates. The average is 1.5 (6/4 = 1.5).

Thank you for your help.
Ptla

[davegri]

Your table structure is Many-to-Many. Have a look at this link to see a comprehensive DB solution for such requirements.
It includes many reports for tracking the member/event assignments.

Many-To-Many, two ways to update Junction table, includes Not In List handling (accessforums.net)

[June7]

Dave, nice demo of forms but don't see how this relates to OP question: "gives the total of specific dates rather than a count of all the records".

One way is to use DCount() domain aggregate function in report header or footer textbox: =DCount("*", "Event", "filter criteria here as appropriate")

If you filter for a specific event, why would there be multiple dates?

Average what? Exactly what does this ratio mean to you?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Ptla]

For example the need for an average is there are multiple dates in a given time period lets say 1 year. Members meet a total of 500 times to one event held at 15 different dates. That gives me an average of 33.3 attending each day (500 / 15 = 33.3).

[CJ_London]

You need two queries, the first to select distinct queries , the second to do your count of dates in this query

[Welshgasman]

Have a query that displays distinct dates for required period.
Use that value from a DCount() of that query to do your math.
You will already have a count of the records in the report.