Hi. Was wondering if I could get a little help on a report I'm trying to create. I'll give a brief description of what I'm working with.
I have created a database for booking rooms within different buildings. On the table Room Bookings there is a record created for every room, for every day within the year in half hour increments. So...for each day, each room has 21 records. There are fields for time, date and room number already completed. There are additional fields for Booked by, booked for and Increment (which autocompletes to .5 when someone books a room given its a half hour time slot).
When a person wants to book a room they see a layout showing the rooms for the day, they enter the times they want the room (e.g. from 3-4 pm) and a find with a loop happens in the background, if the room is not already booked (booked by and booked for fields blank) then it books it for the person and sets the increment field to .5 .
This all works like a charm as I need it to.
My issue is when I try to create a report to give me the percentage of room usage. I also have a field which is a summary (Usage) of the increment field. So if I ran the report to find me the usage of all the rooms in a building for a date range and sort by room it tells me a total of how many hours the room was booked for. Works fine. (See image below. That report is for a three day period) Now I need to do the next step. A room has the capacity to be booked for 7.5 hours in a day. So I need to divide the total Usage by 7.5, multiply by 100 to get the percentage....then divide by the days within the date range (which I get using a variable in my script). My only issue is while the usage field gives me the hours per room in the report, the other field i create to give me the average percentage gives me a total for ALL the rooms booked.
Using the example below what I need is the following: Room 1 was booked for 4 hours over 3 days.
4 / 7.5 = 0.5333 X 100 = 53 .. but then the average over the three days within that report is 53 / 3 = 18% (rounded)
What calculation / summary, whatever do I need to do so that I can get an answer per room in the below report as opposed to a total. of the 6.5 hours?
THank you SO much for any help offered / easier ways of doing this, I really appreciate it. Apologies for the long winded explanation.