Prev: Odd Parameter request in Crosstab
Next: underscore sign in query
From: piku on 23 Mar 2010 10:18
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!
From: piku on 23 Mar 2010 10:36
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!

"piku" wrote:

> 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!
From: vanderghast on 23 Mar 2010 11:04
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


"piku" <piku(a)discussions.microsoft.com> wrote in message
news:7A381FF9-574B-4B8E-99C5-ABB07F3AEBBA(a)microsoft.com...
> 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!

 | 
Pages: 1
Prev: Odd Parameter request in Crosstab
Next: underscore sign in query