Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Booking system (2000)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Booking system (2000)

    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?

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Booking system (2000)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Booking system (2000)

    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.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Booking system (2000)

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

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Booking system (2000)

    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,
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •