Trying to figure out if a certain type of report can be created

[dcf1999]

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

[CJ_London]

I would use a crosstab query

employees would be row header
incidents the column header
sum Time the value

plus sum Time as a row header for the totals column

1. lets call it qryXTab

2. now create a report and add a subform to it (just drag on any existing form).

3. next open the subform control properties and change the sourceobject from whatever the name of the form is to

query.qryXTab

4. Resize the subform control as required and save the report

It is not ideal - you may have to have code to resize the columns etc and I'm not sure about the individual column totals

[dcf1999]

Ok so that works, but you're right, it's not ideal.

It easily allows me to view a month or date range but it has issues with fitting on a page and such if their are a lot of calls for that time frame. This will work for a temp solution until I find a perminant one.

It seems like a simple report. I mean... list the calls for a specific range of dates on top, and the employees on the right and fill in the time value by referencing the employee and call number. Totals are just a sum function. I can do this all day long by using dlookup functions if I want to fill in a field or whatnot, just don't know how to do it in a form. It's really frustrating.

[CJ_London]

but it has issues with fitting on a page

you'll have that issue regardless of the final basis you use. If your boss was to review on screen rather than on paper, he can scroll it horizontally. The maximum width of a report is 22 inches, so say 1" for each column and 3" for the name means the maximum number of incident columns you can show on a printed report is around 18, which is greater than the 16 you mentioned - if it is paper then you'll need to get the sellotape out, or print to say A3 pdf landscape, which when printed to A4 paper will reduce - but may be too small to be readable.

It seems like a simple report

it is - the issue is the scale required. Put it in excel, you'll have the same problem. The issue is the presentation. Perhaps consider names across the top (use initials to keep column width down) and incidents down the side. If you use one of the examples you found for creating a dynamic report, you can rotate the column headers, use different font sizes etc

[Micron]

One month there might be 3 calls and the next there could be 16.

What about people? Maybe put them across the top as that might be somewhat constant.
But really, this likely all comes down to management fighting change that ought to be imposed by the 'limitations' of the nature of a properly designed database/form/report. Would they push you to create a spreadsheet with Word? Not likely - it's just too obvious that it's the wrong tool. Then why can't they accept that this report probably ought to be grouped by person with calls listed in rows for each group/person? All they need to do is learn to read data in another view, and they ought to accept that the fluid nature of the number of calls just doesn't fit with the fact that you will need a set number of controls on the report. For those months you don't use them all, the report will look like a real hatchet job. Design it for 16 and as soon as you need 17, you're redesigning the report. Worse still, you might not even realize you need 17, or 18 or ...

[ssanfu]

PMFJI,
Welcome to the forum....

In Post #1, you display an image of an Excel spreadsheet. It appears that you are confusing "Time" and Duration" (as many people do).
When you write a time, there is a colon in it: 10:00 AM.
When you write a duration, there is NOT a colon: It took me 3.0 hours to walk home.
You WOULD NOT write it as "It took me 3:00 hours to walk home".

This is the table showing duration

January

Call:	2018-01	2018-02	2018-03	2018-04	Totals
Employee 1		1.34		1.0	2.34
Employee 2	0.75	1.34			2.09
Employee 3	0.75		1.08	1.0	1.83
Employee 4		1.34		1.0	2.34
Total:	1.5	4.02	1.08	3.0

0:45 = 45/60 = 0.75 hours
0:20 = 20/60 = 0.3333334 hours
0:05 = 5/60 = 0.0833333 hours