Attendance by event by month report (1 Viewer)

Hi,

I have made myself a database that tracks 60 participants attend or refuse to attend up to 33 different kind of events that occur monthly.

I have the database with the participants, a form to enter participation and refusals (an append function is used here) and a query that totals up my participation by event, by month.

Now I need to report that looks like this:
- 1 page per participant
- shows the entire list of events possible
-their participation and refusal for each month
-shows the above for the entire year, separated by month.

I have received some formal training for Access... however this was in 1999. I don't use it excessively, but can get around it and figure stuff out - and I am stuck now. Any ideas on how I can get this report?

What are your tables and how are they related?

I am guessing you have a tblParticipants and a tblEvents. Do you directly add one entry to tblEvents for each event the participant can go to or is tblEvents a master file that you like participants to?

Also where are you storing the dates they could be attending?

Hi Mark,

I have the following tables:

Attendance
Event
Event Type
ResidentT


Event - Is the list of the 33 event types
EventType - is the table for the groups for the 33 events (for example, one of the groups is Diversional - the events under that are coffee, outing, pub nite, sing song, special events)
The ResidentT is my table to add all the participants and it is a temp holder for the attendance.
Attendance is were my append query moves the data from the ResidentT table to a permanent attendance table where each resident's participation and refusal is tracked

I built this based on this tutorial I found and then customized it for my needs. (Search "Microsoft Access: Tracking Student Attendance, Append Query" in YouTube)

The relationship between my tables is as such:
EventType - linked to Event table and Attendance table
Event - linked to Attendance table

So I am guessing you don't have a fixed list of people who will be invited then?

Do you have one record in your attendance file for each attendee/event OR do you have one for each month with the 33 events? Also do you have a date field in it?

So long as you have a date field you can run your report off of attendance, sort it by both attendee and date, and filter based off of a date range for the month you want.

The list of people who will be participating changes... the participants are actually personal care home residents... who unfortunately, sometimes pass away and a new resident is moved into the personal care home. So although there will always be 60 participants, on occasion, some are new and some are no longer here.

I have 1 record in my attendance database for each attendee and each event and each date. That's what my append query does.

I am able to get my query to separate by month and count for each event the participation and refusal, but am now stuck at how to get my query data to a report that shows what I need.

Participant


These are on my horizontal
April 2016
Participation / Refusal

May 2016
Participation / Refusals


These are my vertical list options
Event type (1 on 1 for example)
coffee
gardening
sing song
Event type (diversional for example)
Pub nite
movies
crafts
visits


And so on for events and for months (I would like a whole fiscal year at a time (from April to March) with all their participation and refusals captured for each event and each month

Sorry - doesn't let me show my table properly and can't post links or attachments yet (10 post minimum)