By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,054 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Have fun (?) with this query request

P: n/a
Dear friends,
I have an mdb with table "Rooms"

RoomID
RoomSize
RoomNumber

and table "Reservations"

ReservationID
RoomNumber
CheckInDate
CheckOutDate

So, one room can have multiple reservations: if we talk about room
(ref. schema above)

10
Single
403

we can have

55
403
10/09/2007
17/09/2007

58
403
15/12/2007
20/12/2007

Given some checkIn date + some checkOut date, I need a sql query to
know if there are available rooms (any size, any number...) in that
period.
Can you help me please?

Thanx a lot!

Sep 7 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Sep 7, 2:02 pm, Nightfall <chesbo...@gmail.comwrote:
Dear friends,
I have an mdb with table "Rooms"

RoomID
RoomSize
RoomNumber

and table "Reservations"

ReservationID
RoomNumber
CheckInDate
CheckOutDate

So, one room can have multiple reservations: if we talk about room
(ref. schema above)

10
Single
403

we can have

55
403
10/09/2007
17/09/2007

58
403
15/12/2007
20/12/2007

Given some checkIn date + some checkOut date, I need a sql query to
know if there are available rooms (any size, any number...) in that
period.
Can you help me please?

Thanx a lot!
Okay, so you want a query that shows all unavailable rooms
(CheckInDate<PotentialCheckOutDate AND
CheckOutDate>PotentialCheckInDate) and then we want to subtract it
from a query which shows all rooms.
I think this should do the trick:
SELECT tblRooms.[RoomNumber] FROM tblRooms WHERE tblRooms.[RoomNumber]
NOT IN
(SELECT DISTINCT tblRooms.[RoomNumber] FROM tblRooms INNERJOIN
tblReservations ON tblRooms.RoomNumber=tblReservations.RoomNumber
WHERE tblReservations.CheckInDate<#" & dPotentialCheckOPutDate & "#
AND tblReservations.CheckOutDate>#" & _
dPotentialCheckInDate & "# " )

Sep 7 '07 #2

P: n/a
On 8 Set, 00:09, OldPro <rrossk...@sbcglobal.netwrote:
On Sep 7, 2:02 pm, Nightfall <chesbo...@gmail.comwrote:


Dear friends,
I have an mdb with table "Rooms"
RoomID
RoomSize
RoomNumber
and table "Reservations"
ReservationID
RoomNumber
CheckInDate
CheckOutDate
So, one room can have multiple reservations: if we talk about room
(ref. schema above)
10
Single
403
we can have
55
403
10/09/2007
17/09/2007
58
403
15/12/2007
20/12/2007
Given some checkIn date + some checkOut date, I need a sql query to
know if there are available rooms (any size, any number...) in that
period.
Can you help me please?
Thanx a lot!

Okay, so you want a query that shows all unavailable rooms
(CheckInDate<PotentialCheckOutDate AND
CheckOutDate>PotentialCheckInDate) and then we want to subtract it
from a query which shows all rooms.
I think this should do the trick:
SELECT tblRooms.[RoomNumber] FROM tblRooms WHERE tblRooms.[RoomNumber]
NOT IN
(SELECT DISTINCT tblRooms.[RoomNumber] FROM tblRooms INNERJOIN
tblReservations ON tblRooms.RoomNumber=tblReservations.RoomNumber
WHERE tblReservations.CheckInDate<#" & dPotentialCheckOPutDate & "#
AND tblReservations.CheckOutDate>#" & _
dPotentialCheckInDate & "# " )- Nascondi testo tra virgolette -

- Mostra testo tra virgolette -
Well done, thank you very much!!!

Nightfall

Sep 8 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.