473,326 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

reservations

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
11 3060
"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
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
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

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
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
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
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
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
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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Fabio | last post by:
Hello, is there any quite easy solution for the problem of transposing the rows into the columns? I have the following table with some data in it: dealer date 09.00 10.00 11.00 ...
0
by: MarionEll | last post by:
Deadline for XML 2004 Hotel Accommodations Nears The deadline for hotel accommodations for the XML 2004 event is next Wednesday, October 20. XML 2004 takes place at the Marriott Wardman Park...
5
by: Mal | last post by:
Hello. I have a database that tracks reservations at a campground. I want to be able to make a calendar type report that shows how many people are here in given period. Stored for each...
3
by: Justin | last post by:
I am trying to query two tables with a stored procedure but I get the following error: "Procedure Details has no parameters and arguments were supplied." Here is my stored procedure as created...
15
by: deercreek | last post by:
I need some help figuring something out. I'm not sure if I heading in the correct direction. What I'm trying to accomplish is the following. I have a table that has id, discrip, and 2 date fields....
2
by: Oliver Bleckmann | last post by:
seems as if the definition in the header file is ignorred `setVon' has not been declared here is the main code and reservation header file the actual part is commented out and repeated at the end...
3
by: anotherjoe | last post by:
Question 1: I have reservation database in access 2007 I have a cabin information table with: cabinid (primarykey) cabinname(text) cabinlocation(text) I have a customer table with:...
0
by: David Goodger | last post by:
If you haven't registered for PyCon yet, now is the time! The early-bird registration deadline is February 20, one week away. After that, the price for registration will be going up. ...
2
by: prakatak7410 | last post by:
#include<iostream> #include<string> #include<cctype> using namespace std; struct SEAT { bool occupied; // initially, false string first,last; // passenger name int numBags; ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.