Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2010
    Posts
    1

    Unanswered: Simple query problem

    Hi everyone,

    This is a pretty straightforward problem but I can't seem to wrap my head around it. I'm pretty much a noob when it comes to Access so bear with me!
    I've got a simple database for a hotel room booking system with 3 tables as below:

    tblCustomer:
    CustomerID
    Forename
    Surname
    etc

    tblBooking:
    BookingID
    CustomerID
    RoomID
    BookingDate

    tblRooms:
    RoomID
    RoomNumber
    RoomType
    RoomCost

    The requirements of the system are pretty basic, each customer who makes a booking only stays for one night at a time in one room.

    What I'm trying to do is set up a way for the user to specify a date, and then have the system return which rooms are free (unbooked) on that date.

    I've got some idea that I need to use a query to do this. I understand how to get it to show me rooms which are booked on that day, just not rooms which are NOT booked on that day.
    I've been banging my head against a wall on this one, can someone please point me in the right direction?

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ...
      FROM tblRoomstabletbl
    LEFT OUTER
      JOIN tblBookingtabletbl
        ON tblBookingtabletbl.RoomID = tblRoomstabletbl.RoomID
       AND tblBookingtabletbl.BookingDate = '2010-09-09'
     WHERE tblBookingtabletbl.RoomID IS NULL
    make sure you understand the difference between the ON conditions (two of them) and the WHERE condition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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