I am a volunteer firefighter on a local fire department and I need assistance on a Fire Call Marks database that I am building.



I need to create a detailed report that will summarize the number of fire calls each member attends each month. It needs to be able to calculate the number of fire calls they attended and the number of possible fire calls they could have attended. I need to create a report that includes the current month and a “rolling” past 12 months.
I have created a sample report to display what I am attempting to create.

Database

tblEvent – stores the details about the event (fire call) – date, time, address, etc
tblEventDetails – stores EventDetailsID, EventID, MemberID, LocationID, Attended (Y/N)
tblEventMaster – is created by a query that stores all records/fields for each member and call, I created this for several reasons but it can be bypassed and the query can be used instead.
tblMembers – stores member information

Additional supporting tables – lookups, etc

Via the frmEvent an event (fire call) is created (EventID) and the details are entered. I then run an append query to add a list of all active members to the tblEventDetails table for the appropriate EventID. Then on the page Members (frmEvent) I am able to select which members attended and the location the responded to, Station or Scene etc.

To this point I am happy with the way things are working except the report. I have tried Crosstab queries but I have had issue with getting the Month in the columns correctly. I also do not see a method to have a crosstab have both the sum of the attended and count the possible number of fire calls, and place both fields in columns.

I have a report – rptEventMaster(Summary) that has a partial of my desired output but it is formatted incorrectly – rows and columns need to be switched for the months.

I also created a query – qryEventPastYear that has the data in the proper column and row format but I need to find out how to get the column headings correct and also include “possible calls” vs just “attended calls”.

Please let me know if this is possible and if so how?

Thanks for any assistance.