Booking System - Vacant room report

[Peter Sutton]

Greetings all,

My booking system includes 2 tables:
tblRooms with the key field RoomID, and some other fields AND a
tblBooked Rooms with a number of fields including RoomID and
BookedDate.

For a given date range, I can generate a vacant room report by
generating a temporary table of all the Rooms and BookedDates using a
cartesian join, and then either deleting the booked rooms, or by
using a 'Not In( )' select query.

Can anyone suggest a solution that does not involve populating a
temporary table?

Peter

[Tom van Stiphout]

On Tue, 18 Mar 2008 13:17:33 +1100, Peter Sutton
<ps*****@killspam.com.auwrote:

All temporary tables can be replaced by queries.
-Tom.

>
Greetings all,

My booking system includes 2 tables:
tblRooms with the key field RoomID, and some other fields AND a
tblBooked Rooms with a number of fields including RoomID and
BookedDate.

For a given date range, I can generate a vacant room report by
generating a temporary table of all the Rooms and BookedDates using a
cartesian join, and then either deleting the booked rooms, or by
using a 'Not In( )' select query.

Can anyone suggest a solution that does not involve populating a
temporary table?

Peter

[DFS]

Tom van Stiphout wrote:

On Tue, 18 Mar 2008 13:17:33 +1100, Peter Sutton
<ps*****@killspam.com.auwrote:

All temporary tables can be replaced by queries.
-Tom.

Rather than building convoluted, nested queries, a report is often more
efficiently produced by intermediate summation of data into temp table(s).

>My booking system includes 2 tables:
tblRooms with the key field RoomID, and some other fields AND a
tblBooked Rooms with a number of fields including RoomID and
BookedDate.

For a given date range, I can generate a vacant room report by
generating a temporary table of all the Rooms and BookedDates using a
cartesian join, and then either deleting the booked rooms, or by
using a 'Not In( )' select query.

Can anyone suggest a solution that does not involve populating a
temporary table?

Sounds like you're already there. Try something like:

SELECT RoomID
FROM tblRooms
WHERE RoomID NOT IN
(SELECT DISTINCT RoomID
FROM tblBookedRooms
WHERE BookedDate #2/15/2008#);

[Peter Sutton]

On Tue, 18 Mar 2008 01:34:53 -0500, "DFS" <nospam@dfs_.comwrote:

>Tom van Stiphout wrote:

>On Tue, 18 Mar 2008 13:17:33 +1100, Peter Sutton
<ps*****@killspam.com.auwrote:

All temporary tables can be replaced by queries.
-Tom.

Rather than building convoluted, nested queries, a report is often more
efficiently produced by intermediate summation of data into temp table(s).

Thank you for your response.

My first approach is to get a solution that works. I have that.

What I am looking for now, is a better one which I think can be
achieved without recourse to temporary tables and consequent bloat in
the database.

Peter

>>My booking system includes 2 tables:
tblRooms with the key field RoomID, and some other fields AND a
tblBooked Rooms with a number of fields including RoomID and
BookedDate.

For a given date range, I can generate a vacant room report by
generating a temporary table of all the Rooms and BookedDates using a
cartesian join, and then either deleting the booked rooms, or by
using a 'Not In( )' select query.

Can anyone suggest a solution that does not involve populating a
temporary table?

Sounds like you're already there. Try something like:

SELECT RoomID
FROM tblRooms
WHERE RoomID NOT IN
(SELECT DISTINCT RoomID
FROM tblBookedRooms
WHERE BookedDate #2/15/2008#);

[DFS]

Peter Sutton wrote:

On Tue, 18 Mar 2008 01:34:53 -0500, "DFS" <nospam@dfs_.comwrote:

>Tom van Stiphout wrote:

>>On Tue, 18 Mar 2008 13:17:33 +1100, Peter Sutton
<ps*****@killspam.com.auwrote:

All temporary tables can be replaced by queries.
-Tom.

Rather than building convoluted, nested queries, a report is often
more efficiently produced by intermediate summation of data into
temp table(s).

Thank you for your response.

My first approach is to get a solution that works. I have that.

What I am looking for now, is a better one which I think can be
achieved without recourse to temporary tables and consequent bloat in
the database.

Peter, You didn't read far enough down in my first response.

SELECT RoomID
FROM tblRooms
WHERE RoomID NOT IN
(SELECT DISTINCT RoomID
FROM tblBookedRooms
WHERE BookedDate #2/15/2008#);

Not using temp tables won't change the 'bloat factor' very much - unless you
rely on them heavily. Access systems bloat in the normal course of
business.

Peter

>>>My booking system includes 2 tables:
tblRooms with the key field RoomID, and some other fields AND a
tblBooked Rooms with a number of fields including RoomID and
BookedDate.

For a given date range, I can generate a vacant room report by
generating a temporary table of all the Rooms and BookedDates
using a cartesian join, and then either deleting the booked rooms,
or by using a 'Not In( )' select query.

Can anyone suggest a solution that does not involve populating a
temporary table?

Sounds like you're already there. Try something like:

SELECT RoomID
FROM tblRooms
WHERE RoomID NOT IN
(SELECT DISTINCT RoomID
FROM tblBookedRooms
WHERE BookedDate #2/15/2008#);

[Peter Sutton]

On Wed, 19 Mar 2008 11:06:38 -0500, "DFS" <nospam@dfs_.comwrote:

>Peter Sutton wrote:

>On Tue, 18 Mar 2008 01:34:53 -0500, "DFS" <nospam@dfs_.comwrote:

>>Tom van Stiphout wrote:
On Tue, 18 Mar 2008 13:17:33 +1100, Peter Sutton
<ps*****@killspam.com.auwrote:

All temporary tables can be replaced by queries.
-Tom.

Rather than building convoluted, nested queries, a report is often
more efficiently produced by intermediate summation of data into
temp table(s).

Thank you for your response.

My first approach is to get a solution that works. I have that.

What I am looking for now, is a better one which I think can be
achieved without recourse to temporary tables and consequent bloat in
the database.

Peter, You didn't read far enough down in my first response.

SELECT RoomID
FROM tblRooms
WHERE RoomID NOT IN
(SELECT DISTINCT RoomID
FROM tblBookedRooms
WHERE BookedDate #2/15/2008#);

You are correct, I did not see this bit at the bottom of your
message.

However, say RoomID 2 is booked on Feb 17 but not after, I think your
sub query would show this room booked for the whole period.

>
Not using temp tables won't change the 'bloat factor' very much - unless you
rely on them heavily. Access systems bloat in the normal course of
business.

Yes I know about bloat. Where I use temporary tables, they are always
put in temporary MDBs which the system blows away every say 20 logins
and recreates.

Peter

>

>>

>>>>My booking system includes 2 tables:
tblRooms with the key field RoomID, and some other fields AND a
tblBooked Rooms with a number of fields including RoomID and
BookedDate.
>
For a given date range, I can generate a vacant room report by
generating a temporary table of all the Rooms and BookedDates
using a cartesian join, and then either deleting the booked rooms,
or by using a 'Not In( )' select query.
>
Can anyone suggest a solution that does not involve populating a
temporary table?

Sounds like you're already there. Try something like:

SELECT RoomID
FROM tblRooms
WHERE RoomID NOT IN
(SELECT DISTINCT RoomID
FROM tblBookedRooms
WHERE BookedDate #2/15/2008#);