Need help on Query!

[gMAC]

I'm building a booking database that requires many reports. One report need to show what rooms and dates the rooms are available. I need a query that will return dates the rooms are not booked. Put simply if room A-1 was rented 1/5/03(InDate) to 1/10/03(OutDate) and then rented 1/14/03(InDate) to 1/20/03(OutDate) my query should return 1/10/03 to 1/14/03. I built a nested query that will return these dates, but it also returns all the other greater InDates for the same OutDate. Here is an example.
Query1:
SELECT tblReservation.UnitNumber, tblReservation.DateIn, tblReservation.DateOut
FROM tblReservation
ORDER BY tblReservation.DateIn;

Query2:
SELECT DISTINCT tblReservation.UnitNumber, First(tblReservation.DateIn) AS FirstOfDateIn, Query1.DateOut
FROM tblReservation INNER JOIN Query1 ON tblReservation.UnitNumber = Query1.UnitNumber
WHERE (((tblReservation.DateOut)>[Query1].[DateOut] And (tblReservation.DateOut)>[Query1].[DateIn]))
GROUP BY tblReservation.UnitNumber, Query1.DateOut;

I can't figure out how to just get just the next indate and ignore the rest. Any ideas?
Thanks
~gMAC~

 

[Jon K]

Try this query:-

SELECT UnitNumber, DateIn, DateOut,
IIf(DateOut in (Select DateIn from tblReservation where UnitNumber=a.UnitNumber), Null, DateOut) AS [Available_From],
(Select Min(DateIn) from tblReservation where UnitNumber=a.UnitNumber and DateIn>a.DateOut) AS [To]
FROM tblReservation AS a
ORDER BY UnitNumber, DateIn;


It returns the dates of [Available_From] and [To] between the records.

Hope it helps.

 

[gMAC]

Wow!! Thanks Jon K,
I've been trying to figure that out for three days! It's much appreciated.
~gMAC~