Re: Booking System - Vacant room report

Please see my response at the end

On Thu, 20 Mar 2008 05:36:24 -0700 (PDT), Roger
<lesperancer@xxxxxxxxxx> wrote:

On Mar 20, 4:15 am, Peter Sutton <psut...@xxxxxxxxxxxxxxx> wrote:
On Wed, 19 Mar 2008 06:54:04 -0700, Tom van Stiphout





<no.spam.tom7...@xxxxxxx> wrote:
On Wed, 19 Mar 2008 23:35:11 +1100, Peter Sutton
<psut...@xxxxxxxxxxxxxxx> wrote:

The temp table was filled by some query, let's for simplicity say
insert into TempTable
 select * from Customers
Then you currently query from the temp table:
select * from TempTable
 where State='AZ'

To eliminate the temp table:
select * from Customers
 where State='AZ'

This is of course a simple example, but you can see how parts of the
two queries are combined to eliminate the temp table.

-Tom.

Thank you for taking the time to respond. I have respect for your
input as I know you have been a long time occupant of this newsgroup
with many fine contributions.

However, I don't think you fully understand my issue.

If you have the time and patience to continue addressing this, let me
put the issue in very simple terms.  If I have a table showing showing
RoomID and certain dates, then for a given date range, how do I create
a query to show  RoomID and the dates that it does not exist.

Peter





On Mon, 17 Mar 2008 20:58:35 -0700, Tom van Stiphout
<no.spam.tom7...@xxxxxxx> wrote:

On Tue, 18 Mar 2008 13:17:33 +1100, Peter Sutton
<psut...@xxxxxxxxxxxxxxx> wrote:

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

Thank you for your response.

The basis of my post was that I don't know how to create such queries.
Can you give me any assistance in this respect?

Peter

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

so if we're looking at a date range of #2/15/2008# to #2/20/2008#
and room id 2 is booked on #2/17/2008# and #2/18/2008#

you want to see what, in what format ?
something like this ?

room available
2 2/15/2008 2/16/2008 2/19/2008 2/20/2008

My report currently shows the output something like

Vacant Rooms between Apr 15 and May 15 2008

Tuesday Apr 15 Room: 2, 4, 6
Wed Apr 16 Room: 1,2,7, 9

etc

I was more focussed on getting the source data, not the display
format.

Peter
.

Relevant Pages

  • Re: Proper use of update query
    ... Or, you could run your "temp" query as a make table query & then run the UPDATE against the new table, using it as if it were the temp query. ... UPDATE Customers ... I wish to update this filed according to each customer's total order. ...
    (microsoft.public.access.queries)
  • Re: Booking System - Vacant room report
    ... The temp table was filled by some query, ... select * from Customers ... RoomID and certain dates, then for a given date range, how do I create ...
    (comp.databases.ms-access)
  • Re: Booking System - Vacant room report
    ...  select * from Customers ... Then you currently query from the temp table: ... two queries are combined to eliminate the temp table. ... BookedDate. ...
    (comp.databases.ms-access)
  • Re: Booking System - Vacant room report
    ...  select * from Customers ... Then you currently query from the temp table: ... two queries are combined to eliminate the temp table. ... BookedDate. ...
    (comp.databases.ms-access)
  • Re: Booking System - Vacant room report
    ... The temp table was filled by some query, ... select * from Customers ... BookedDate. ...
    (comp.databases.ms-access)