Forum Discussion
Count days separated by month
Sorry if I didn’t give enough information – my bad.
I am creating a database for a friend who has a small hotel – I’m not getting paid, but have been promised a good dinner.
She has a number of rooms in her hotel.
The rooms rent out for different amounts – depending on demand.
She would like to know what the bookings were per month.
But this, obviously, has a problem as she may book out a room for (for example) 6 weeks, 2 weeks, 4 days, etc, etc. so on some occasion the bookings would go from one month to another.
So rather than using the start date (e.g. 25/12/2020 for X days) it would be better if she could know what the bookings where per month.
I hope this makes sense.
Using my original post’s details let say some booked a room from 28/08/2021 & 02/10/2021
She would want to know that the room was booked for:
Aug 21 = for 4 days
Sep 21 = for 30 days
Oct 21 = for 2 days
I have tried to create a query to do this but, let just say, I’m not have much luck.
Thanks
Thank you. Now that does make sense.
You need to have each booking recorded one of three ways.
Each booking could have a start date and and end date. I.e. a booking for three days starting on December 1st would have an end date of December 3.
Each booking could be recorded on each of the days in the time span, i.e. a booking starting on December 1st would have entries for that booking on 1/12, 2/12 and 3/12
It sounds like your approach is to record the Start date and a second field with the length of stay. That's why you are having a problem with the calculation across month boundaries where you need to know "bookings" per month. A booking could be in two different months, or even three if it is for more than 30 (or 31) days.
I think you also need to pin down what you and your client mean by "bookings". Do you mean the number of times a room is booked for one day or longer? Or, do you mean the number of nights for which a room is booked? Slightly different meanings. Let's say 10 people each "book" the same room for two nights each. That's 10 "bookings" by the first definition, or 20 "bookings" by the second definition. Before we get to a specific suggestion, we do need to pin down that definition.
For me, the second approach mentioned above, one record per room per day it is booked, would probably be the easiest way to manage calculations like this, but it could be done with the other approach, as long as we know what we are counting exactly.