Who has the solution to this problem ??????

[Ellada]

Hello,
I am building a hotel reservation system. For this query there are two
tables which i used "tblRoom" and "tblReservation".

The query below [qryRoomsReserved] shows me the rooms which are reserved
betweeen two dates:

PARAMETERS [Please enter arrival date] DateTime, [Please enter checkout
date] DateTime;
SELECT tblReservation.[Room No], tblReservation.[Booked From],
tblReservation.[Booked Until], tblRoom.[Room Type]
FROM tblRoom INNER JOIN tblReservation ON tblRoom.[Room No] =
tblReservation.[Room No]
WHERE (((tblReservation.[Booked From]) Between [Please enter arrival
date] And [Please enter checkout date]-1)) OR ((([Booked Until]-1) Between
[Please enter arrival date] And [Please enter checkout date])) OR
(((tblReservation.[Booked From])<[Please enter arrival date]) AND (([Booked
Until]-1)> [Please enter checkout date]-1));

The above works fine. BUT I want a query which will show me the rooms which
are avaliable (not reserved) between two dates. So, basically the inverse of
the above. Here's the problem.

I have tried the following to inverse the above query but with no luck:

SELECT tblRoom.[Room No]
FROM tblRoom LEFT JOIN qryRoomsReserved ON tblRoom.[Room No] =
qryRoomsReserved.[Room No]
WHERE ((([qryRoomsReserved].[Room]) Is Null));

Can anyone see the problem ?? Would be grateful for your knowledge.
Please tell me if u need more information about tables or anything else.
Nick.

 

[Dale Fye]

Ellada,

The problem with your logic for the second part of the topic is that you can
have two different parties reserved into the same room, on the same day, so
long as one is checking in and the other is checking out, so just doing the
query the way you indicate will mean that you end up with rooms being vacant
one night between occupants. You need to modify the original query so that
it disregards those occupants that are checking out on your arrival date,
and those that are checking in on your departure date. Rewritten this way,
your second query should work.

PARAMETERS [Please enter arrival date] DateTime, _
[Please enter checkout date] DateTime;
SELECT tblReservation.[Room No], tblReservation.[Booked From],
tblReservation.[Booked Until], tblRoom.[Room Type]
FROM tblRoom
INNER JOIN tblReservation
ON tblRoom.[Room No] = tblReservation.[Room No]
WHERE [Booked From] < [Please enter checkout date]
AND [Booked Until] > [Please enter arrival date]


--
HTH

Dale


HTH
Dale

 

[Ellada]

Dale,
Thanks alot. U have solved my problem. 2nd query is working now.
Thanks again,
Ellada

Ellada,

The problem with your logic for the second part of the topic is that you can
have two different parties reserved into the same room, on the same day, so
long as one is checking in and the other is checking out, so just doing the
query the way you indicate will mean that you end up with rooms being vacant
one night between occupants. You need to modify the original query so that
it disregards those occupants that are checking out on your arrival date,
and those that are checking in on your departure date. Rewritten this way,
your second query should work.

PARAMETERS [Please enter arrival date] DateTime, _
[Please enter checkout date] DateTime;
SELECT tblReservation.[Room No], tblReservation.[Booked From],
tblReservation.[Booked Until], tblRoom.[Room Type]
FROM tblRoom
INNER JOIN tblReservation
ON tblRoom.[Room No] = tblReservation.[Room No]
WHERE [Booked From] < [Please enter checkout date]
AND [Booked Until] > [Please enter arrival date]


--
HTH

Dale


HTH
Dale

Hello,
I am building a hotel reservation system. For this query there are two
tables which i used "tblRoom" and "tblReservation".

The query below [qryRoomsReserved] shows me the rooms which are reserved
betweeen two dates:

PARAMETERS [Please enter arrival date] DateTime, [Please enter checkout
date] DateTime;
SELECT tblReservation.[Room No], tblReservation.[Booked From],
tblReservation.[Booked Until], tblRoom.[Room Type]
FROM tblRoom INNER JOIN tblReservation ON tblRoom.[Room No] =
tblReservation.[Room No]
WHERE (((tblReservation.[Booked From]) Between [Please enter arrival
date] And [Please enter checkout date]-1)) OR ((([Booked Until]-1) Between
[Please enter arrival date] And [Please enter checkout date])) OR
(((tblReservation.[Booked From])<[Please enter arrival date]) AND (([Booked
Until]-1)> [Please enter checkout date]-1));

The above works fine. BUT I want a query which will show me the rooms which
are avaliable (not reserved) between two dates. So, basically the

inverse

of
the above. Here's the problem.

I have tried the following to inverse the above query but with no luck:

SELECT tblRoom.[Room No]
FROM tblRoom LEFT JOIN qryRoomsReserved ON tblRoom.[Room No] =
qryRoomsReserved.[Room No]
WHERE ((([qryRoomsReserved].[Room]) Is Null));

Can anyone see the problem ?? Would be grateful for your knowledge.
Please tell me if u need more information about tables or anything else.
Nick.

 

[Dale Fye]

You're welcome.

Dale,
Thanks alot. U have solved my problem. 2nd query is working now.
Thanks again,
Ellada

Ellada,

The problem with your logic for the second part of the topic is that you can
have two different parties reserved into the same room, on the same day, so
long as one is checking in and the other is checking out, so just doing the
query the way you indicate will mean that you end up with rooms being vacant
one night between occupants. You need to modify the original query so that
it disregards those occupants that are checking out on your arrival date,
and those that are checking in on your departure date. Rewritten this way,
your second query should work.

PARAMETERS [Please enter arrival date] DateTime, _
[Please enter checkout date] DateTime;
SELECT tblReservation.[Room No], tblReservation.[Booked From],
tblReservation.[Booked Until], tblRoom.[Room Type]
FROM tblRoom
INNER JOIN tblReservation
ON tblRoom.[Room No] = tblReservation.[Room No]
WHERE [Booked From] < [Please enter checkout date]
AND [Booked Until] > [Please enter arrival date]


--
HTH

Dale


HTH
Dale

Hello,
I am building a hotel reservation system. For this query there are two
tables which i used "tblRoom" and "tblReservation".

The query below [qryRoomsReserved] shows me the rooms which are reserved
betweeen two dates:

PARAMETERS [Please enter arrival date] DateTime, [Please enter checkout
date] DateTime;
SELECT tblReservation.[Room No], tblReservation.[Booked From],
tblReservation.[Booked Until], tblRoom.[Room Type]
FROM tblRoom INNER JOIN tblReservation ON tblRoom.[Room No] =
tblReservation.[Room No]
WHERE (((tblReservation.[Booked From]) Between [Please enter arrival
date] And [Please enter checkout date]-1)) OR ((([Booked Until]-1) Between
[Please enter arrival date] And [Please enter checkout date])) OR
(((tblReservation.[Booked From])<[Please enter arrival date]) AND (([Booked
Until]-1)> [Please enter checkout date]-1));

The above works fine. BUT I want a query which will show me the rooms which
are avaliable (not reserved) between two dates. So, basically the

inverse

of
the above. Here's the problem.

I have tried the following to inverse the above query but with no luck:

SELECT tblRoom.[Room No]
FROM tblRoom LEFT JOIN qryRoomsReserved ON tblRoom.[Room No] =
qryRoomsReserved.[Room No]
WHERE ((([qryRoomsReserved].[Room]) Is Null));

Can anyone see the problem ?? Would be grateful for your knowledge.
Please tell me if u need more information about tables or anything else.
Nick.