Hello all!
First, this is my first post of asking for help. I try very hard to figure things out on my own but i'm stumped on where to even start or if a report like what i want can even be created (I'm sure it can). So here are the details:
I volunteer for a fire dept in my town and they tasked me with creating a database that tracks a number of different things. I'm pretty good with access and VB and I have no problems with doing advanced stuff. I, however, am not good with reports besides basic things. The main report I need to make is a report showing what calls we had for a specific month, who was on each call and how long the call was. They used to do it in excel manually and I'd like to do the same format. So...
I have 2 tables:
- Incidents - this tracks the incident. Has all the info like call type (ems, fire), Call number, was mutual aid requested, etc...
- Incident Sub - this is a sub database that is for apparatus times. Each vehicle that that goes out gets the personal that left on the vehicle, and it's times logged (when it left, when it arrived, when it left for hospital, when it arrived back in quarters). Since their may be multiple vehicles going out on 1 call, the best way, i thought, was to create a separate table that links to the ID of the [incidents] table. Then they can select the vehicle that left and it's times, and make a new entry for the next vehicle (if that tall makes sense)
Now the report:
I'd like to create a report that has the employee names on a column to the left, along the top will be the call number. Each column (after the employees name) will the the total amount of time they spent on that specific call. The last column will be a total column which sums up the total amount of time that employee was on calls for that month. The last row would then sum up the total man hours for that call.
I attached a pic of what i'm trying to achieve. Is a report like this possible? is it somewhat easy or does it require multiple queries? I've created a cross tab query that somewhat did what i wanted but I would need something that the chief could select a month or date range and it creates a report using the calls from the month or range and I couldn't figure out how to incorporate a cross tab into a report like that. I found some examples using static fields, but it's usually for a given amount of columns... in this application the amount of calls along the top row would vary. One month there might be 3 calls and the next there could be 16.
I crated a simple query that pulls the values from the tables i just don't know how to physically format the report to have the rows and columns as described. Any help would be greatly appreciated.
Maybe if this is too complicated I could talk to the chief about making a simpler format for the report.
Thanks,
Dave