Results 1 to 7 of 7
  1. #1
    Join Date
    May 2013
    Posts
    4

    Question Unanswered: Date Range Reports - Help Needed!

    Hi guys, I've just started building an Access 2003 database and I'm racking my brains about something (it's been a while since I last touched Access).

    The database is for booking rooms in temporary accommodation. When a customer wants to book a room for a specific date range (eg 01/01/13 to 20/01/13), the user needs to be able to search for rooms that are not booked within that date range. This query will then provide the user with room IDs that are available between those dates.

    I'm unsure as to whether this can be accomplished with a QBF alone, or a new table in the relationship between Bookings and Rooms is required. I have attached a snapshot of the current relationships.

    I have hit a brick wall with this, any ideas would be most appreciated.

    Thanks!
    Attached Thumbnails Attached Thumbnails relationships.JPG  

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The joins look reasonable here.

    What do you get when you try it?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2013
    Posts
    4
    I can get a query to show bookings that fall on a particular date, but can't work out how to show rooms that do not have a booking against them for a particular date.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    So, use the query that shows bookings in a left join with the table of rooms. Show all tables that don't have a record in the query.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    May 2013
    Posts
    4
    So I've got a query that returns all rooms that do conflict with start/end dates, but I need to display the inverse of that (all RoomIDs that are not in the query). I can't seem to get the second query to work with a left join.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Please post the SQL statements and some sample outputs - makes it easier to see what's going on.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    May 2013
    Posts
    4

    Smile

    I've managed to get something to work, using a form and two queries. The form has text fields for the dates to search. The first query looks like this:

    Code:
    SELECT Rooms.RoomID, BookingDetails.BookingStartDate, BookingDetails.BookingEndDate, BookingDetails.CustomerID, Rooms.LocationID
    FROM Rooms INNER JOIN BookingDetails ON Rooms.RoomID=BookingDetails.RoomID
    WHERE (((Forms!DateQueryForm!txtDateQuery) Between BookingDetails!BookingStartDate And BookingDetails!BookingEndDate)) Or (((Forms!DateQueryForm!txtDateQuery2) Between BookingDetails!BookingStartDate And BookingDetails!BookingEndDate));
    This shows any bookings which clash with the input dates. The second query compares these values to the Rooms table, and shows all rooms that are not present in the first query:

    Code:
    SELECT Rooms.RoomID, Rooms.RoomTypeID, Rooms.Comments, Rooms.LocationID
    FROM Rooms LEFT JOIN AvailableRoomsQuery ON Rooms.RoomID = AvailableRoomsQuery.RoomID
    WHERE (((Rooms.LocationID)=[Forms]![DateQueryForm]![txtLocation]) AND ((AvailableRoomsQuery.RoomID) Is Null));
    Not the prettiest solution but I think it works for the time being.

Tags for this Thread

Posting Permissions

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