VERY CHALLENGING! SHOWING PRODUCT AVAILABILITY

[Guest]

Hi!

I'm doing a uni assignment which is based on a boat hire company. I'm really
stuck at the moment (beginnning to hate ms access!). Obviously when a boat
is hired out, it is not available to other customers. i have at the moment, a
hire booking for where the boat id, hire start date and hire end date are
inputted along with other things. how on eartth do i make sure that a boat is
not hired out to another customer at the same time??????????????????
(preferably without vb programming! - if possible?)

your help would be highly appreciated. if you need more information, let me
know.

Thanks in advance!

Sf

 

[Allen Browne]

The simplest way to do this is to set the minimum granularity, e.g. bookings
are for 15-minute periods only, and if someone hires it for an hour you
block our 4 bookings. It is then very easy to discover duplicates, because
they are exact matches.

If that is not acceptable, the basic idea is that two events overlap if:
B starts before A ends, AND
A starts before B ends.
You can therefore create a WHERE clause to look for that condition.
More info:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html

 

[Paul Overway]

One way to do this would be to take the start and end date/time that has
been requested, and determine if either the start or the end time fall
BETWEEN the start and end date/time that the boat has already be hired,
i.e.,

WHERE ([BoatRequestStart] Between [BoatHiredStart] And [BoatHiredEnd])
OR ([BoatRequestEnd] Between [BoatHiredStart] And [BoatHiredEnd])

This method is accurate. However, you could have an issue with the way
people want to enter the reserved time slots. For example, if the boat
reservation is entered as 2:00PM to 3:00PM and someone requests 3:00PM to
4:00PM, the query would indicate that the boat is already reserved...and it
really is, but people tend not to consider the 1 minute of overlap to be of
any consequence even though technically the boat is not available for that
minute. If you wanted to accomodate for this, you could use the following
instead:

WHERE (([BoatRequestStart] > [BoatHiredStart]) AND
([BoatRequestStart]<[BoatHiredEnd]))
OR (([BoatRequestEnd] > [BoatHiredStart]) AND
([BoatRequestEnd]<[BoatHiredEnd]))

 

[Guest]

Thanks for your help…your tips are very useful…congrats on making such a
wonderful site!

I was scrolling through trying to find out whether there was anything
related to another part of my assignment but I couldn’t find anything (unless
I wasn’t looking in the right place). This same boat company undertakes
repairs as well but can only undertake repairs if boats are out on hire (each
boat has a location id). I need to find a way to only allow repairs if a
location id is available on a certain date. Any ideas?

Thanks

SF

 

[Allen Browne]

To prevent the user saving the record about the repair if the boat is in the
wrong place, you could DLookup() the location in the BeforeUpdate event of
the form.

Basic help on DLookup():
http://allenbrowne.com/casu-07.html

 

[Guest]

I don't think dlookup along could be used to solve the problem. here is some
more info that might make things clearer.

The boatyard has a single slipway, and moorings for up to five boats at any
one time. The available capacity imposes a constraint on the number of
repair/refit jobs which can be undertaken at any one time (a theoretical
maximum of two boats out of the water and a further five either being worked
on at their moorings if the repairs are minor or waiting to be taken out of
the water). This theoretical maximum assumes all MSE’s boats are out on hire
and therefore not needing to use the moorings. The system, therefore, will
have to keep track of which repair bays (on land) and/or moorings are
available at any given time.

 

[Allen Browne]

So, you will need fields to track the where the boats are, and how many of
each type of places there is, and use DCount() to see how many there are in
the place you want to know about at any given time.