Booking system (2000)

[andrewgibsonsw]

I have a table with a booking date, booking start and end time and a room number. For example, Room A is booked between 10:00 and 11:00 on 12th April. I now need to find if a room is already booked out for the specified date and time. I thought I might build a Find Duplicates query and play around with the SQL, but it looks a bit tricky. Any other ideas?

[HansV]

Let's assume you want to find out if Room A is available on 4/12/02 from 10:00 to 11:00. These data have been entered in text boxes (or combo boxes) on an unbound form frmBooking.

Room A is booked during ate least a part of that period if there is an entry in the bookings table for 4/12/02 with (start before 10:00 AND end after 10:00) OR (start before 11:00 AND end after 11:00).

This leads to a SQL statement like (this is just made up out of air, so there might be typos)

SELECT Count(*) FROM tblBookings
WHERE Room = Forms!frmBooking!txtRoomID AND Bookdate = Forms!frmBooking!txtBookDate AND ((StartTime < Forms!frmBooking!txtStartTime AND EndTime > Forms!frmBooking!txtStartTime) OR (StartTime < Forms!frmBooking!txtEndTime AND EndTime > Forms!frmBooking!txtEndTime));

If it returns 0, the room is available; if > 0, the room is booked.

In the code behind the form, you might use the WHERE part of this SQL statement in a DCount:

strWhere = "Room = Forms!frmBooking!txtRoomID AND BkDate = Forms!frmBooking!txtBkDate AND ((StartTime < Forms!frmBooking!txtStartTime AND EndTime > Forms!frmBooking!txtStartTime) OR (StartTime < Forms!frmBooking!txtEndTime AND EndTime > Forms!frmBooking!txtEndTime))"

IsBooked = (DCount("*", "tblBooking", strWhere) > 0)

Does this help you on your way?

[andrewgibsonsw]

That sounds excellent. Another tricky question: how would I now build a form that looks like a calendar that blocks out the booked slots. I essentially need to know how I can automatically generate the sequence of times and room numbers and put them in a grid layout. Any suggestions?? Thanks, Andy.

[HansV]

I suspect that building a graphical booking system in Access will be a headache. You might be consider looking for a readymade application, or switching to VB6. Or perhaps Outlook/Exchange?

If you want to do it in Access, you might build on the very incomplete db I attached. It 's a long way from what you want to accomplish, but it might give you some ideas.

[charlotte]

There's no reason it should be harder in Access than in VB. At least the database connection is simpler in Access.

[WendellB]

It seems to me that one of Alison Balter's books has a fairly detailed example of a booking system - I don't remember the title, and may have the author wrong, but I'm sure I've seen one. There a quite a few complications in such systems - many of the commercial products out there are a wee bit flakey too. But check out some of the samples. Hope this helps,