• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

Reservation Query SQL

I have recently been tring to model a hotel reservations database I found the following query online it says it is supposed to query my reservations table for available rooms by date:

SELECT *
FROM tblReservations
WHERE Room_Id NOT in (select Room_Id from tblReservations where     RequestedStartDate <= EndBookingDate and RequestedEndDate >= StartBookingDate );

Question # 1
I am new to sql parameter queries and  what I don't undestand is why I have to input a start bookingdate and an endbookingdate. Shouldn't the user just add the input the requested dates the guest would like to find the rooms available within the time frame

Question # 2
I would like to know if i can insert use this query in a form where the user inserts the request dates via a combo boxes and calendar control, clicks a control button and gets the results in another form.Is this possible using vbcode as well?

 
0
wkellya
Asked:
wkellya
1 Solution
 
Jeffrey CoachmanCommented:
wkellya,

Assume the following Data in the tblReservations.

ReservationID,Room_ID,StartBookingDate,End Booking Date
1,100,1/1/2006,1/15/2006
2,100,1/20/2006,1/30/2006
3,100,2/10/2006,2/20/2006
4,200,1/5/2006,1/10/2006
5,200,3/1/2006,3/8/2006
6,300,10/1/2006,11/12/2006

The query works as expected and asks for a Start and End dates.
(Isn't that what you wanted?)

One note though, if a room is not in the Reservations table, it won't ever be displayed as being free.

For example: let's say you have another room, 400. Notice that it is not in tblReservations, because it has NO reservations, so it will not be listed in the query as being free.

So in this case you also need a a table for the Rooms (tblRooms)
Room_ID, RoomCategory, RoomName...ect

This new table must also be included in the query to show rooms that may not have any reservations at all. (Thus they are "available")

Now as far as your second question...
Yes, you can do this with VBA.

Is this question really only worth 55 points to you?
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now