View unavailable
in [Queries]
|
From: piku on 22 Mar 2010 10:24 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
From: John Spencer on 22 Mar 2010 10:51 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 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
From: ghetto_banjo on 22 Mar 2010 10:56 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.
From: KARL DEWEY on 22 Mar 2010 11:09 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())); -- Build a little, test a little. "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
From: piku on 23 Mar 2010 04:48
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 |