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]

Re: Booking System - Vacant room report

On Tue, 18 Mar 2008 13:17:33 +1100, Peter Sutton
<psutton@killsp am.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]

Re: Booking System - Vacant room report

Tom van Stiphout wrote:

On Tue, 18 Mar 2008 13:17:33 +1100, Peter Sutton
<psutton@killsp am.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]

Re: Booking System - Vacant room report

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

>Tom van Stiphout wrote:

>On Tue, 18 Mar 2008 13:17:33 +1100, Peter Sutton
><psutton@kills pam.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]

Re: Booking System - Vacant room report

Peter Sutton wrote:

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

>Tom van Stiphout wrote:

>>On Tue, 18 Mar 2008 13:17:33 +1100, Peter Sutton
>><psutton@kill spam.com.auwrot e:
>>>
>>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]

Re: Booking System - Vacant room report

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

>Peter Sutton wrote:

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

>>Tom van Stiphout wrote:
>>>On Tue, 18 Mar 2008 13:17:33 +1100, Peter Sutton
>>><psutton@kil lspam.com.auwro te:
>>>>
>>>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
>>>>BookedDat e.
>>>>>
>>>>For a given date range, I can generate a vacant room report by
>>>>generatin g 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#);

>