For example, the caravan park has 12 sites. Each site can be booked for a number of days throughout the year e.g. a weekend.
What i want to be able to do is provide a booking system to enable this. But at the moment i'm experiencing double bookings. The query doesn't seem to recognise the logic of the situation.
An example is: I want to book a caravan into site 5 for a weekend fri-sun, once i press the button return avaiable sites, i want it to return all sites that have no bookings on that site between those dates. I i create a query showing me the sites that are not avaible it works ok, but i want it to return me a list of the sites that ARE avaiable on those dates. I seem to be unable to invert the code.
Here's the SQL for the query that returns the sites that are not available. I want to invert this:
SELECT DISTINCT SITE.[Site#], SITE.Description
FROM SITE LEFT JOIN RESERVE ON SITE.[Site#] = RESERVE.[Site#]
GROUP BY SITE.[Site#], SITE.Description, RESERVE.Arr_date, RESERVE.Dept_date, RESERVE.[Site#]
HAVING (((RESERVE.Arr_date) Not Between [Forms]![SITE_RESERVATIONS]![Arr_date] And [Forms]![SITE_RESERVATIONS]![Dept_date] And (RESERVE.Arr_date)>Now())) OR (((RESERVE.Dept_date) Not Between [Forms]![SITE_RESERVATIONS]![Arr_date] And [Forms]![SITE_RESERVATIONS]![Dept_date] And (RESERVE.Dept_date)>Now())) OR (((RESERVE.[Site#]) Is Null));