By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

reservations

P: n/a
Been asked to create an app to keep records for a horse business.
Just getting my head round what it needs. Most is straight forward.

Need to create a routine to deal with reservations. Some things, like a box
for your horse will be by the week. Others, such as use of the arena will be by
the hour. Any thoughts on the neat ways of doing this.
TIA
David B
Hexham UK

Nov 12 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
"David B" <da***@marleycotenospam.fsnet.co.uk> wrote in message news:<bm**********@news6.svr.pol.co.uk>...
Been asked to create an app to keep records for a horse business.
Just getting my head round what it needs. Most is straight forward.

Need to create a routine to deal with reservations. Some things, like a box
for your horse will be by the week. Others, such as use of the arena will be by
the hour. Any thoughts on the neat ways of doing this.
TIA
David B
Hexham UK


See Albert Kallal's notes on this before you start. I did something
like this once, and it's enough to warp your brain if you're not used
to it. If I remember right, the logic is kinda inside out. The notes
are on his web page. I think you just create reservation records with
a start date/time and and end date/time and then use logic to
determine when you have available slots.
Nov 12 '05 #2

P: n/a
The simplest way to do this is to programatically create a record for each
"period" in the booking. For the horse, a record for each date would be the
best approach. For the arena it would be a record for each hour. It's really
important to understand that this minimum granularity (day or hour) is very
difficult to change later, so make sure the owner *never* wants to do half
hour bookings (for example).

Once there is a record for each "period" within the booking, it's quite
simple to query the periods, list them, apply charges, etc. The code to
create them is a VBA loop, e.g. using the AddNew method (DAO).

The other alternative is completely flexible bookings where a person can
take out any period of day/hours and parts of. This is considerably more
complex to query, and would take more work to achieve. If you need to
consider this option, there's a starter on how to locate the clashing events
in this article:
http://allenbrowne.com/appevent.html
Note that this article is not relevant if you go with the first option
(fixed granularity).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"David B" <da***@marleycotenospam.fsnet.co.uk> wrote in message
news:bm**********@news6.svr.pol.co.uk...
Been asked to create an app to keep records for a horse business.
Just getting my head round what it needs. Most is straight forward.

Need to create a routine to deal with reservations. Some things, like a box for your horse will be by the week. Others, such as use of the arena will be by the hour. Any thoughts on the neat ways of doing this.
TIA
David B
Hexham UK

Nov 12 '05 #3

P: n/a
A few things. (I have written quite a few reservation systems).

Resist the temptation to create "blank" records for the time slots.

So, you most certainly can and will build a table of "horse boxes".

However, to book into those boxes, simply create a record with the start
time/date, and the end time/date. That way, you can book for just one hour,
or a whole week into that box with one record (use a timedate field for the
start, and the end).

To prevent overlaps, you simply test for collisions in the records with a
query.

In my "old" type reservation systems one would often create a whole bunch of
time slots. This take tons of code.

By just adding a record *when* you make a booking, you save tons of code, as
you don't have to try and update a bunch of slots.

You also read some notes of mine, and I also show a er diagram at the end of
the article.

http://www.attcanada.net/%7ekallal.m...000000003.html
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn
Nov 12 '05 #4

P: n/a

Albert D. Kallal <ka****@msn.com> wrote in message
news:Gwrhb.55933$9l5.11196@pd7tw2no...
A few things. (I have written quite a few reservation systems).

Resist the temptation to create "blank" records for the time slots.

So, you most certainly can and will build a table of "horse boxes".

However, to book into those boxes, simply create a record with the start
time/date, and the end time/date. That way, you can book for just one hour,
or a whole week into that box with one record (use a timedate field for the
start, and the end).

To prevent overlaps, you simply test for collisions in the records with a
query.

In my "old" type reservation systems one would often create a whole bunch of
time slots. This take tons of code.

By just adding a record *when* you make a booking, you save tons of code, as
you don't have to try and update a bunch of slots.

You also read some notes of mine, and I also show a er diagram at the end of
the article.

http://www.attcanada.net/%7ekallal.m...000000003.html
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

Thanks for the replies folks.
One little quirk of this operation is that when people take a stable the booking
is open ended. It ends when the horse dies or is sold or they fall out with the
owners !
How would that affect querying the start and end date ?
David B

Nov 12 '05 #5

P: n/a
Make the StartDate field required, but permit bookings with no EndDate.

In your queries, handle the nulls. For example:
Between [StartDate] And Nz([EndDate], #1/1/2999#)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"David B" <da***@marleycotenospam.fsnet.co.uk> wrote in message
news:bm**********@newsg1.svr.pol.co.uk...

Albert D. Kallal <ka****@msn.com> wrote in message
news:Gwrhb.55933$9l5.11196@pd7tw2no...
A few things. (I have written quite a few reservation systems).

Resist the temptation to create "blank" records for the time slots.

So, you most certainly can and will build a table of "horse boxes".

However, to book into those boxes, simply create a record with the start
time/date, and the end time/date. That way, you can book for just one hour, or a whole week into that box with one record (use a timedate field for the start, and the end).

To prevent overlaps, you simply test for collisions in the records with a query.

In my "old" type reservation systems one would often create a whole bunch of time slots. This take tons of code.

By just adding a record *when* you make a booking, you save tons of code, as you don't have to try and update a bunch of slots.

You also read some notes of mine, and I also show a er diagram at the end of the article.

http://www.attcanada.net/%7ekallal.m...000000003.html
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn
Thanks for the replies folks.
One little quirk of this operation is that when people take a stable the

booking is open ended. It ends when the horse dies or is sold or they fall out with the owners !
How would that affect querying the start and end date ?
David B

Nov 12 '05 #6

P: n/a
Another question. How do you suggest turning a booking into a chargeable event.
It is possible that the original booking needs ammending. eg they initially
booked the arena for 2 hours but on the day, because it was available, used it
for 3 hours
David B

Allen Browne <ab***************@bigpond.net.au> wrote in message
news:Wa********************@news-server.bigpond.net.au...
Make the StartDate field required, but permit bookings with no EndDate.

In your queries, handle the nulls. For example:
Between [StartDate] And Nz([EndDate], #1/1/2999#)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"David B" <da***@marleycotenospam.fsnet.co.uk> wrote in message
news:bm**********@newsg1.svr.pol.co.uk...

Albert D. Kallal <ka****@msn.com> wrote in message
news:Gwrhb.55933$9l5.11196@pd7tw2no...
A few things. (I have written quite a few reservation systems).

Resist the temptation to create "blank" records for the time slots.

So, you most certainly can and will build a table of "horse boxes".

However, to book into those boxes, simply create a record with the start
time/date, and the end time/date. That way, you can book for just one hour, or a whole week into that box with one record (use a timedate field for the start, and the end).

To prevent overlaps, you simply test for collisions in the records with a query.

In my "old" type reservation systems one would often create a whole bunch of time slots. This take tons of code.

By just adding a record *when* you make a booking, you save tons of code, as you don't have to try and update a bunch of slots.

You also read some notes of mine, and I also show a er diagram at the end of the article.

http://www.attcanada.net/%7ekallal.m...000000003.html
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

Thanks for the replies folks.
One little quirk of this operation is that when people take a stable the

booking
is open ended. It ends when the horse dies or is sold or they fall out

with the
owners !
How would that affect querying the start and end date ?
David B



Nov 12 '05 #7

P: n/a
You may want separate fields for BookingHours and ActualHours? Or you may
just want to record a dollar amount. It depends how you charge.

If it's a really simple system, and you want to invoice them at the end of a
month, you could include these fields in the booking:
BookingFee Currency the amount charged
InvoiceID Number foreign key to tblInvoice.InvoiceID.

The InvoiceID is blank while it's uninvoiced. At the end of the month, run
some code that generates an invoice for all clients who have bookings where
InvoiceID is blank. These become the line items of the invoice.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"David B" <da***@marleycotenospam.fsnet.co.uk> wrote in message
news:bm**********@news8.svr.pol.co.uk...
Another question. How do you suggest turning a booking into a chargeable event. It is possible that the original booking needs ammending. eg they initially booked the arena for 2 hours but on the day, because it was available, used it for 3 hours
David B

Allen Browne <ab***************@bigpond.net.au> wrote in message
news:Wa********************@news-server.bigpond.net.au...
Make the StartDate field required, but permit bookings with no EndDate.

In your queries, handle the nulls. For example:
Between [StartDate] And Nz([EndDate], #1/1/2999#)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"David B" <da***@marleycotenospam.fsnet.co.uk> wrote in message
news:bm**********@newsg1.svr.pol.co.uk...

Albert D. Kallal <ka****@msn.com> wrote in message
news:Gwrhb.55933$9l5.11196@pd7tw2no...
> A few things. (I have written quite a few reservation systems).
>
> Resist the temptation to create "blank" records for the time slots.
>
> So, you most certainly can and will build a table of "horse boxes".
>
> However, to book into those boxes, simply create a record with the start > time/date, and the end time/date. That way, you can book for just
one hour,
> or a whole week into that box with one record (use a timedate field
for the
> start, and the end).
>
> To prevent overlaps, you simply test for collisions in the records
with a
> query.
>
> In my "old" type reservation systems one would often create a whole

bunch of
> time slots. This take tons of code.
>
> By just adding a record *when* you make a booking, you save tons of

code, as
> you don't have to try and update a bunch of slots.
>
> You also read some notes of mine, and I also show a er diagram at
the end of
> the article.
>
> http://www.attcanada.net/%7ekallal.m...000000003.html
>
>
> --
> Albert D. Kallal (MVP)
> Edmonton, Alberta Canada
> ka****@msn.com
> http://www.attcanada.net/~kallal.msn
>
Thanks for the replies folks.
One little quirk of this operation is that when people take a stable
the booking
is open ended. It ends when the horse dies or is sold or they fall out

with the
owners !
How would that affect querying the start and end date ?
David B


Nov 12 '05 #8

P: n/a
da***@marleycotenospam.fsnet.co.uk (David B) wrote in
<bm**********@news8.svr.pol.co.uk>:
Another question. How do you suggest turning a booking into a
chargeable event. It is possible that the original booking needs
ammending. eg they initially booked the arena for 2 hours but on
the day, because it was available, used it for 3 hours


My parking garage app automatically generates invoices for the
default amount (your 2 hours) and if the billing exceeds that, the
user generates an ad hoc invoice for the extra amount. This works
because it's pretty exceptional.

I think Allen's idea is best, to have a BookingHours and an
ActualHours field. The ActualHours field could either be Null or
initialized to the same value as BookingHours. If Null, you'd just
use Nz(ActualHours, BookingHours) to get the amount. If you
ActualHours to the same value and then change it if it's different,
then you could always use ActualHours for your billing.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #9

P: n/a
Another question. This app will have bookings for a number of different things,
eg stable by week. arena hire by hour. cross country gallop by hour, horse hire
by day and so on. Are there any potential problems storing all of these in the
same tblbookings?
David B

Nov 12 '05 #10

P: n/a
It is *very* desirable to put all these bookings into one table, with a
field (drop-down box) to distinguish the BookingType. Doing so will make
your program more flexible (easy to add other booking types later) and the
billing process much easier (records all in one table).

However, you may need to keep the weekly stable bookings in their own table.
If you use a minimum granualarity of 1 hour, you would get 168 records per
week for the stable bookings, not to mention the problem of the open-ended
bookings.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David B" <da***@marleycotenospam.fsnet.co.uk> wrote in message
news:bm**********@news8.svr.pol.co.uk...
Another question. This app will have bookings for a number of different things, eg stable by week. arena hire by hour. cross country gallop by hour, horse hire by day and so on. Are there any potential problems storing all of these in the same tblbookings?
David B

Nov 12 '05 #11

P: n/a
"David B" <da***@marleycotenospam.fsnet.co.uk> wrote in message news:<bm**********@news8.svr.pol.co.uk>...
Another question. This app will have bookings for a number of different things,
eg stable by week. arena hire by hour. cross country gallop by hour, horse hire
by day and so on. Are there any potential problems storing all of these in the
same tblbookings?
David B


Person reserves Object should work. If you have a Date/Time
field for Reservation.Begin and Reservation.End, I don't see the
problem. the fact that different "objects" can be reserved for
periods of different durations I think is irrelevant. You would still
check for collisions if you used Albert's method anyway... (Well, if
I remember his post right... I remember getting confused by the
thinking inside out thing.)
Nov 12 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.