Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2001
    Location
    Blackpool, Lancashire, England
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    bookings query (SQL) (xp)

    The following query yields a table of rooms booked between two dates, but what I wish is a table of the rooms not booked between those date. At this stage it pointlessly joins the 2 tables though I would imagine that to yield what I require a join will be required. The tblAccomodation table is a list of rooms and facilities, and the tblBookings is a table recording bookings of these rooms.

    SELECT *
    FROM tblAccomodation INNER JOIN tblBookings ON tblAccomodation.AccomID = tblBookings.AccomID
    WHERE (((tblBookings.ReservationDate)>=[Forms]![frmMain]![txtArrDate]) AND ((tblBookings.DepartureDate)<=[Forms]![frmMain]![txtDepDate]));

    Help Thx JasH

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: bookings query (SQL) (xp)

    This is actually quite difficult. The INNER JOIN is not the way to go. It can give you a list of bookings that were within that date or not within that date, but that's not what you want.

    Essentially what you want is a list of Rooms that had no booking records between those dates! Therefore, base your query on your Rooms table, then use Dcount or a subquery to get the count of booking records that occurred within your date range. Then set the selection critieria to =0. So, if the count is not zero for a room, that means it had a booking within that date range and won't be on the list.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    May 2001
    Location
    Blackpool, Lancashire, England
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: bookings query (SQL) (xp)

    Chrs, you put me roughly on the right track with the final result being the following query

    SELECT tblAccomodation.AccomID, tblAccomodation.TypeOfAccom, tblAccomodation.NoSleeps, tblAccomodation.Tariff, tblAccomodation.DisabledAccess, tblAccomodation.Smoking, tblAccomodation.Notes
    FROM tblAccomodation
    WHERE NOT tblAccomodation.AccomID IN (SELECT AccomID
    FROM tblBookings
    WHERE tblBookings.ReservationDate>=Forms!frmMain!txtArrD ate And tblBookings.DepartureDate<=Forms!frmMain!txtDepDat e OR tblBookings.ReservationDate<Forms!frmMain!txtArrDa te And tblBookings.DepartureDate>Forms!frmMain!txtArrDate And tblBookings.DepartureDate<=Forms!frmMain!txtDepDat e OR tblBookings.ReservationDate>=Forms!frmMain!txtArrD ate AND tblBookings.ReservationDate<Forms!frmMain!txtDepDa te And tblBookings.DepartureDate>Forms!frmMain!txtDepDate
    GROUP BY AccomID);

    Thx JasH

Posting Permissions

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