dates between

[Bibi]

I have a rental database - There are three primary tables. Properties,
Leases and Tenants. The Leases table has two date fields - Arrive and Depart
-There are multiple properties that may be leased. I need to be able to
identify which dates are available for lease and which are reserved for each
property. The Arrive Date is leased, the depart date is not leased and there
may be any number of dates in between which are leased. As a stop gap measure
I have created a table and manually enter each date as Reserved when a lease
is signed, but even this does not show me at a glance which dates are
AVAILABLE for which properties and that's what I need to be able to see at a
glance. Any suggestions?
TIA
Bibi

 

[Allen Browne]

You want to select the properties that are not leased. Those are the ones
that have no record where today is between the Arrive and Depart dates.

SELECT Properties.*
FROM Properties
WHERE NOT EXISTS
(SELECT Leases.LeaseID
FROM Leases
WHERE (Leases.PropertyID = Properties.PropertiesID)
AND (Date() >= Leases.Arrive)
AND (Date() < Leases.Depart));

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

An alternative approach would be to:
1. Create a query based on the Leases table.
2. Drag PropertyID into the grid.

3. In a fresh column of the Field row, enter:
Date()

4. In the Criteria row beneath this:
Between [Arrive] And ([Depart] - 1)

5. Save the query.

6. Use the Unmatched query wizard (first dialog when you create a new query)
to make a query selecting the properties from the Properties table that are
not in that query.