Posted by Erland Sommarskog on 09/29/14 11:33

Buzby (gb@pumpupthe.net) writes:
> I have two tables - RoomTypes and Availability. The query below brings
> back the information I need - except I need to filter it one more time
> and exclude any room that has a 0 in a field call AvailDayShow (in the
> Availability table) in it across a selected date range. (It's basically
> looking across a date range and if a room value is 0 for AvailDayShow in
> this range the rooms not bookable so don't show it)
>
> SELECT DISTINCT
> RoomTypes.RoomID,
> RoomTypes.RoomHotelID,
> RoomTypes.RoomType,
> RoomTypes.RoomCode,
> RoomTypes.RoomDescription,
> RoomTypes.RoomIncludes,
> Availability.*
> FROM
> (RoomTypes
> INNER JOIN Availability ON
> RoomTypes.RoomID = Availability.AvailRoomID)
>
> Where (Availability.AvailDate between #12/09/2005# AND #12/13/2005#)

Add

AND NOT EXISTS
(SELECT *
FROM Availability A2
WHERE A.AvailRoomID = A2.AvailRoomId
AND A2.AvailDate between #12/09/2005# AND #12/13/2005#
AND A2.AvailDayShow = 0)

If this does not work out, please post:

o CREATE TABLE statements for your tables. Don't forget the keys.
o INSERT statements with sample data.
o The desired result given the sample.

And of course, if you post to this group, you will get syntax for SQL
Server, which may or may not work on Access.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]