View unavailable

  • Thread starter Thread starter piku
  • Start date Start date
P

piku

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
 
Easiest way would be to use your table of rooms and the query that shows the
available rooms in an unmatched query. There is a query wizard that will do that.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
if you want to see what rooms are currently in use, you could do
something like this:


SELECT RoomID, IncomingDate, OutgoingDate FROM tblOrders WHERE
IncomingDate <= Date() AND OutgoingDate >= Date()




you could substitute whatever date you want if you replace the Date()
function to see what rooms are unavailable on a given date.
 
Most folks would want to know what was available and you did not say when so
this say which rooms are not available today. Create a table named
CountNumber with field CountNUM containing numbers from 0 (zero) through your
maximum spread.

SELECT CustomerOrders.RoomID
FROM CustomerOrders, CountNumber
WHERE (((DateAdd("d",[CountNUM],[Incomming]))<=[Outgoing]) AND
((DateAdd("d",[CountNUM],[Incomming]))=Date()));
 
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!!!
 
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
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 said:
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
Click to expand...
Click to expand...
 
Back
Top