Re: Booking System - Vacant room report

On Thu, 20 Mar 2008 10:48:35 -0500, "DFS" <nospam@xxxxxxxx> wrote:

Peter Sutton wrote:

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.

huh?

Apologies if my question was not clear.


You'll have to substitute the actual dates and use Between or <=>

(BookedDate Between #2/15/2008# And #3/15/2008#)
(BookedDate >= #2/15/2008# And BookedDate <= #3/15/2008#)

If a room is booked for any one night in the range in the sub query,
then it's ID will be returned from the subquery. Therefore, it will
not appear in the results of the main query.

So your query will return rooms that were not booked for any day in
the date range whereas I am looking for days that rooms are vacant in
the period.

Your query does of course work for a single day. So I suppose I could
generate a report based on the top 30 records of any table but having
the report generate data for 30 consecutive days and having sub
reports based on a single day query.

But as I like to keep things simple, I think I'll stick to my temp
table.

Thanks for taking the time to respond.

Peter
.

Relevant Pages

  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • Re: Fields in Label dont give correct information
    ... You need to create a query based on tblRegEvent and tblRegPayment. ... -If your DCountis to count values in your report, ... half the time with very few errors (other than that label problem I had.) ... For example in the Room assignment on the report an idividual will be ...
    (microsoft.public.access.reports)
  • Re: Tips on domain aggregate replacements
    ... This already is a split db, but the users are 400 miles away from the server hosting the data:) They are all running the same copy of the FE locally on a single terminal server via TS/RDP sessions. ... It doesn't seem to have much impact on performance; the report takes just about as long to run if they are all logged in as it does when I am logged on testing it at night. ... I would do DSums from the controls on the report on the data returned by the query. ... The biggest offender is a certain report that needs to Sum a particular complex total for each of the next twelve months (the DSum in VBA was a sideline to this issue) and present these as items on each line of output. ...
    (microsoft.public.access.modulesdaovba)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)