Prev: Combine Format function?
Next: How to Strip HTML tags from data
From: John Spencer on 23 Mar 2010 08:57
SELECT DISTINCT Rooms.RoomID
FROM Rooms INNER JOIN (Documents INNER JOIN RoomsInDocument ON
Documents.DocumentNo = RoomsInDocument.DocumentNo) ON Rooms.RoomID =
RoomsInDocument.RoomId
WHERE (((Documents.IncomingDate) Between [Forms]![FOrders]![IncDate] And
[Forms]![FOrders]![OutDate])) OR (((Documents.OutgoingDate) Between
[Forms]![FOrders]![IncDate] And [Forms]![FOrders]![OutDate]));

Using the above query as QueryOne (or whatever name you choose to store it
under) you can use the following:

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

An alternative query (probably slower with large amounts of data)
SELECT Rooms.RoomID
FROM Rooms
WHERE Rooms.RoomID NOT IN
(SELECT RoomsInDocument.RoomID
FROM Documents INNER JOIN RoomsInDocument
ON Documents.DocumentNo = RoomsInDocument.DocumentNo
WHERE (Documents.IncomingDate Between [Forms]![FOrders]![IncDate]
And [Forms]![FOrders]![OutDate])
OR (Documents.OutgoingDate Between [Forms]![FOrders]![IncDate]
And [Forms]![FOrders]![OutDate]))



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

piku wrote:
> Ooo, I'm sorry, I replased the queries...
>
> The unavailable rooms query is:
>
> SELECT DISTINCT Rooms.RoomID
> FROM Rooms INNER JOIN (Documents INNER JOIN RoomsInDocument ON
> Documents.DocumentNo = RoomsInDocument.DocumentNo) ON Rooms.RoomID =
> RoomsInDocument.RoomId
> WHERE (((Documents.IncomingDate) Between [Forms]![FOrders]![IncDate] And
> [Forms]![FOrders]![OutDate])) OR (((Documents.OutgoingDate) Between
> [Forms]![FOrders]![IncDate] And [Forms]![FOrders]![OutDate]));
>
> How can I change the query that return list of available rooms?
>
> I tried to do this query but the sheet stay empty.
> The query is:
>
> 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)));
>
> What i need to change?
>
> Thanks!!!
>
> "piku" wrote:
>
>> I'm have 2 tabels.
>> The first table have list of rooms and the second table have list of orders
>> of customers(content RoomId feild and the fields incoming date and outgoing
>> date).
>> I created a query that show all the availables rooms(by Distinct sql
>> function) but i want to show all the unavailables rooms.
>>
>> How can i create query that show all the unavilable rooms?
>>
>> Full Thanks!!!
>> Pik
First  |  Prev  | 
Pages: 1 2
Prev: Combine Format function?
Next: How to Strip HTML tags from data