vehicle booking database
- Thread starter msrahman
- Start date
MarkK
bit cruncher
- Local time
- Today, 00:09
- Joined
- Mar 17, 2004
- Messages
- 8,440
Hello and welcome to the forum
I would set up the availability of your resources like a bank account, so you make deposits and withdrawals on certain days. So a booking looks like the withdrawal a quantity of resource on a certain day, and then a deposit of that same quantity of resource again on the end of the booking.
Then, just like a bank balance, you sum the deposits and withdrawals up to the day you want to check availability for. That is the "balance" of resources in that "account." In the case of real-world items, of course, you can never have a negative balance in this account.
I would set up the availability of your resources like a bank account, so you make deposits and withdrawals on certain days. So a booking looks like the withdrawal a quantity of resource on a certain day, and then a deposit of that same quantity of resource again on the end of the booking.
Then, just like a bank balance, you sum the deposits and withdrawals up to the day you want to check availability for. That is the "balance" of resources in that "account." In the case of real-world items, of course, you can never have a negative balance in this account.
- Local time
- Today, 08:09
- Joined
- Jul 9, 2003
- Messages
- 17,385
This recent post may be of interest:-
Calendar display on a form?
>>>Extract<<<
Afternoon all,
I'm building a bespoke car rental system to replace a 20 year old piece of software (yep, 20!)
Calendar display on a form?
>>>Extract<<<
Afternoon all,
I'm building a bespoke car rental system to replace a 20 year old piece of software (yep, 20!)
- Local time
- Today, 00:09
- Joined
- Aug 30, 2003
- Messages
- 36,269
I've created multiple limousine and taxi reservation/dispatch systems. This may help with the double-booking:
http://www.baldyweb.com/OverLap.htm
in this case incorporating time of course.
http://www.baldyweb.com/OverLap.htm
in this case incorporating time of course.
- Local time
- Today, 08:09
- Joined
- Jul 9, 2003
- Messages
- 17,385
I am currently constructing an educational course based on showing various periods (Car Hire, Dates, Times) on the screen. I got the idea for the course from the original thread that I posted a link to:- HERE...
My take on it is slightly different than pbaldy's. Over the years it has become apparent to me that you get the most power, the most flexibility from MS Access if you think vertically. If you apply this vertical thinking to this problem you end up with something like this:-
Extract START (from my Course) >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
The data for one of the cars (let's say Car 1) could be:-
From: ... To:
01/07/2016 ... 03/07/2016
05/07/2016 ... 07/07/2016
09/07/2016 ... 12/07/2016
13/07/2016 ... 15/07/2016
17/07/2016 ... 21/07/2016
24/07/2016 ... 29/07/2016
It would be tempting to put the dates side by side, a field for “from” and a field for “to”. My experience tells me that it's better to list things, other words you take advantage of the database structure itself. You can calculate and move through a single field easily if it’s vertical. It is difficult if you try coding things horizontally.
Think Vertically
In this case thinking about it vertically, what have we got? We have got a list of dates and we can assume that the first and second date represent the first hire. We don't care care when this Hire happened, it does not have to be between the dates we want to display on the screen, just as long as we know it was the first hire period.
Now I hope you can see what I'm thinking, if the first pair of dates represents the first hire period then the second pair of dates represents the second hire period and so on.
If we cycle through the records in ascending order by date we know that the first date we come to is the start of the first hire and we also know if we numbered the dates as we went the first date would be (1) the second date would be (2) the next date would be (3) and the fourth date (4).
There's a Pattern!
You can see straight away there is a pattern, and if you've watch my other videos you will know I am always looking for patterns! The pattern here is that odd numbers represent the start date and even numbers represent the end date. We can use a built-in functions of VBA to tell if a number is odd or even. That means we can tell if a date is a start date or an end date.
The VBA MOD Function
The way to tell if it is odd or even is to you the use the VBA function mod like this:-
Extract from the Code:-
If X Mod 2 = 0 Then 'Odd Numbers are Start Dates and Even Numbers are End Dates
dateEnd = datHireDate
Else
dateStart = datHireDate
End If
It might not be clear yet because I’m looping through a loop and returning each date to the variable:- “datHireDate”. I am also creating an ascending x value like this:-
X = X + 1
X represents the row number and will change from odd to even as we go.
Extract END >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
The construction, the logic of the table, the means of storing a hire period relies on the fact that start dates will be odd numbers and end dates will be even numbers. It also dictates that if a vehicle is on hire you cannot put it on another hire in the same period, that would not make sense. But adding any dates to the hire list table will mean locating periods when a vehicle is not on hire how will this be done?
First you would need to check that the prospective start date appears in a period which is not within a hire. Let's call This period an “Off Hire” period.
Recall from the previous code the list of hire dates is enumerated and the odd numbers indicate start dates and the even numbers indicate end dates so it then becomes obvious that to find an off hire period you have to find dates which are between an even number and odd number, not an odd number and even number.
Imagine doing this manually:-
You would have a list of dates starting with the first hire date, the start date of the first hire and then you would have the date of the off hire. You want to start a vehicle hire on a particular date. Start at the beginning of the list until you find the dates, one before the prospective hire date and the one after that date. Look at the date before, find its index, remember, it's counted from the first date as being odd and every other odd number being the start date of a hire. Therefore if you look at the index number, if it's an odd number then it's a start date and therefore the next number will be indexed even and will be the end date this means you cannot hire the vehicle because it's already on hire.
My take on it is slightly different than pbaldy's. Over the years it has become apparent to me that you get the most power, the most flexibility from MS Access if you think vertically. If you apply this vertical thinking to this problem you end up with something like this:-
Extract START (from my Course) >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
The data for one of the cars (let's say Car 1) could be:-
From: ... To:
01/07/2016 ... 03/07/2016
05/07/2016 ... 07/07/2016
09/07/2016 ... 12/07/2016
13/07/2016 ... 15/07/2016
17/07/2016 ... 21/07/2016
24/07/2016 ... 29/07/2016
It would be tempting to put the dates side by side, a field for “from” and a field for “to”. My experience tells me that it's better to list things, other words you take advantage of the database structure itself. You can calculate and move through a single field easily if it’s vertical. It is difficult if you try coding things horizontally.
Think Vertically
In this case thinking about it vertically, what have we got? We have got a list of dates and we can assume that the first and second date represent the first hire. We don't care care when this Hire happened, it does not have to be between the dates we want to display on the screen, just as long as we know it was the first hire period.
Now I hope you can see what I'm thinking, if the first pair of dates represents the first hire period then the second pair of dates represents the second hire period and so on.
If we cycle through the records in ascending order by date we know that the first date we come to is the start of the first hire and we also know if we numbered the dates as we went the first date would be (1) the second date would be (2) the next date would be (3) and the fourth date (4).
There's a Pattern!
You can see straight away there is a pattern, and if you've watch my other videos you will know I am always looking for patterns! The pattern here is that odd numbers represent the start date and even numbers represent the end date. We can use a built-in functions of VBA to tell if a number is odd or even. That means we can tell if a date is a start date or an end date.
The VBA MOD Function
The way to tell if it is odd or even is to you the use the VBA function mod like this:-
Extract from the Code:-
If X Mod 2 = 0 Then 'Odd Numbers are Start Dates and Even Numbers are End Dates
dateEnd = datHireDate
Else
dateStart = datHireDate
End If
It might not be clear yet because I’m looping through a loop and returning each date to the variable:- “datHireDate”. I am also creating an ascending x value like this:-
X = X + 1
X represents the row number and will change from odd to even as we go.
Extract END >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
The construction, the logic of the table, the means of storing a hire period relies on the fact that start dates will be odd numbers and end dates will be even numbers. It also dictates that if a vehicle is on hire you cannot put it on another hire in the same period, that would not make sense. But adding any dates to the hire list table will mean locating periods when a vehicle is not on hire how will this be done?
First you would need to check that the prospective start date appears in a period which is not within a hire. Let's call This period an “Off Hire” period.
Recall from the previous code the list of hire dates is enumerated and the odd numbers indicate start dates and the even numbers indicate end dates so it then becomes obvious that to find an off hire period you have to find dates which are between an even number and odd number, not an odd number and even number.
Imagine doing this manually:-
You would have a list of dates starting with the first hire date, the start date of the first hire and then you would have the date of the off hire. You want to start a vehicle hire on a particular date. Start at the beginning of the list until you find the dates, one before the prospective hire date and the one after that date. Look at the date before, find its index, remember, it's counted from the first date as being odd and every other odd number being the start date of a hire. Therefore if you look at the index number, if it's an odd number then it's a start date and therefore the next number will be indexed even and will be the end date this means you cannot hire the vehicle because it's already on hire.
