sql . need a genius

[kevinnice]

I have a table for holidays at houses.
Each record has a house_ref,start_date_of_holiday, end_date_of_holiday etc.
(I have another table (houses) which is related by house_ref.)

I want to have a form which shows which houses are available for a particular period.
ie user specifies start date and end date of query, asking the question: Which houses are available for rent in that period?

To me it's just a head-shag...

Any genius can help me?

Rodney

[pbaldy]

Does this get you started?

http://www.baldyweb.com/OverLap.htm

[kevinnice]

Thanks baldy, it's a partial answer. But I have 1000 booking records, for a variety of 100 houses in my db. So it's no not like saying "is a particular house available from1st June to 13th June'" It's saying "which houses (of the 100) are available in that period".

[pbaldy]

With that technique you find which houses are booked. Using the Unmatched query wizard with the houses table against that query leaves you with the available houses.

[kevinnice]

Thanks again. I can get the logically "counter-intuitive" query. So if I go through my 100 bookings, using this logic, I can determine which bookings fall INSIDE the period, rendering the houses they refer to NOT AVAILABLE.

It's the bringing it togetehr part that i have trouble with: Say I start with my table of 100 villas - do I just use that example to disqualify them? And, if so, how do I code that?

Have to say - thanks again.

[pbaldy]

Like I said, use the Unmatched query wizard. It will ask for two sources, and basically return all the items that are in the first source that aren't in the second. In your case, the first source is your table of 100 houses, the second is the query from above.

Edit: The wizard creates what's often called a "frustrated left join"