2 quesions about query

[piku]

hello!

I have 3 tabels:
1. Rooms-list of rooms
2. Document-list of orders
3. RoomsInDocument-list rooms in one order

I am trying to create query that show all the rooms that don't have order
between Incoming date and Outgoing date.

I have situation the room not have a order.

The query return me empty sheet with option to insert new room.

Two questions:
1. If one room in Rooms table don't have rows in RoomInDocument table the
query don't show him? I think it must to show it when the Rooms table is the
main in the query.

2. How can I show too the all rooms that don't have rows in the
RoomInDocument table and unavailable room between 2 date? to example show all
the room that don't have order between 04/01/2010(IncDate) and
04/15/2010(OutDate).

my query:

SELECT Rooms.RoomID
FROM Rooms INNER JOIN (RoomsInDocument INNER JOIN Documents ON
RoomsInDocument.DocumentNo=Documents.DocumentNo) ON
Rooms.RoomID=RoomsInDocument.RoomId
WHERE (((Documents!IncomingDate Not Between Forms!FOrders!IncDate And
Forms!FOrders!OutDate)) And ((Documents!OutgoingDate Not Between
Forms!FOrders!IncDate And Forms!FOrders!OutDate)));

Please, your help!
Thank you!

 

[piku]

OK, John Spencer help me in my firs quesion.

the answer:
SELECT Rooms.RoomID
FROM Rooms LEFT JOIN queryOne
ON Rooms.RoomID = Queryone.RoomID
WHERE QueryOne.RoomId is Null


Thank you again!

 

[vanderghast]

For the second question, it could be a matter to find rooms which HAVE some
occupancy between the two dates, and then, from somewhere else, returning
all the possible rooms, find those not in the first set.


If fields are Starting and Ending, and if your dates limits for the interval
are StartDate and EndDate, then

SELECT DISTINCT roomID
FROM occupancies
WHERE startDate < Ending AND endDate >= Starting


should list the rooms having a reservation. (You may adjust the < to <= or
the >= to > if the limit are inclusive or exclusive).

Vanderghast, Access MVP