Hairdressing Appointment Database

[rab_dobbie]

Hi there

I am in the process of developing a hairdressing
database for a friend, it had started off as a real
world project but due to certain pitfalls my friend
has decided not to go through with it.

I on the other hand would like to complete it as a pet
project.

I would appreciate it if you could furnish me with any
sites or help concerning the possibility of
appointment clashing and how to stop an appointment
being booked for example 9.10am when an appointment
has been made for 9.00am to 9.20am - I would like to
have this blocked but still able to book for 9.10 for
a quick 10 minute haircut as if the style is a perm there will be
free time for the stylist to complete several short appointments
withing that
time frame.

Your help and advice would be much appreciated.

Rab.

 

[Arvin Meyer [MVP]]

Check out this sample database:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='AppointmentsAlert.mdb'

 

[Albert D. Kallal]

The trick in a booking system is to only store the start and end date of the
booking.


And, to prevent collisions, the logic here is quite simple:


A collision occurs when:


RequestStartDate <= EndDate
and
RequestEndDate >= StartDate


The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.


dim strWhere as string
dim dtRequeestStartDate as date
dim dtRequestEndDate as date


dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")


strWhere="#" & format(dtRequestStartDate,"mm/­dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtRequestEndDate,"mm/dd­/yyyy") & "# >= StartDate"


if dcount("*","tableBooking",strW­here) > 0 then
msgbox "sorry, you can't book
....bla bla bla....


The above is just an example, and I am sure you would build a nice form that
prompts the user for the booking dates. However, what is nice here is that
the simple condition above does return ANY collision. If you
use date + time fields, the above will work equally well for time during a
day.

 

[rabby]

The trick in a booking system is to only store the start and end date of the
booking.

And, to prevent collisions, the logic here is quite simple:

A collision occurs when:

RequestStartDate <= EndDate
and
RequestEndDate >= StartDate

The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.

dim strWhere                        as string
dim dtRequeestStartDate      as date
dim dtRequestEndDate         as date

dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")

strWhere="#" & format(dtRequestStartDate,"mm/­dd/yyyy") & "# <= EndDate" & _
     " and #" & format(dtRequestEndDate,"mm/dd­/yyyy") & "#  >= StartDate"

if dcount("*","tableBooking",strW­here) > 0 then
    msgbox "sorry, you can't book
...bla bla bla....

The above is just an example, and I am sure you would build a nice form that
prompts the user for the booking dates. However, what is nice here is that
the simple condition above does return ANY collision. If you
use date + time fields, the above will work equally well for time during a
day.

Thanx for the advice/help
I will give this a go over the weekend.

Rab.