Sum Room occupancies between dates (count days occupied)

[CutAndPaste]

Actually, it's Property (as in Houses) rather than Rooms but as "Property" has another meaning in the Access context....

Here's what I've got (simplified version):

Properties Table
PropertyID (PK)
PropertyName

People Table
PersonID (PK)
PersonName
PersonCategory

Occupancy Table
OccupancyID (PK)
PersonID (FK)
PropertyID (FK)
OccupancyStartDate
OccupancyEndDate

A form with two unbound fields
ReportStartDate
ReportEndDate

Some business Rules:
A new occupancy for a property can't start on the same day as the previous occupancy ends (so days should not be "double counted")

For each Property:
Some Occupancies may start before ReportStartDate and end after ReportEndDate.
Some Occupancies may start after ReportStartDate and end after ReportEndDate.
Some Occupancies may start after ReportStartDate and end before ReportEndDate.
Any gaps between an OccupancyEndDate and an OccupancyStartDate need to be counted as "Voids".

For any reporting defined by period as defined by ReportStartDate and ReportEndDate, "Occupied Days" + "Voids" = "Number of days in period"

The important part in later reporting is to remove the "Void" days from the calculations as I want to get a measure of actual occupancy breakdown by PersonCategory for the days the Property was actually available, not the total of days between ReportStartDate and ReportEndDate.

I can write a query that calcualtes the durations of Occupancies for each property quite easily if they start and end between the ReportStartdate and the ReportEnddate (OccupancyEndDate - OccupancyStartDate). But how do I factor in an OccupancyStartDate before the ReportStartdate? I tried using an IIF statement but this wouldn't work, (though this may be my fault in the IIF construction).
Duration:IIf("(IsNull[OccupancyEndDate])","Now() - [StartDate]","[EndDate]-[StartDate]")

I want be able to count all of the days the property was occupied between the dates and get a count of the void dates. Can anyone point me in the right direction in terms of query design to get this going?

Simon

 

[Jon K]

If what you want is the number of occupied days, you don't need to count the void days. See the query in the attached database.


If you want to show the number of void days too, you can add the following two fields to the Select Clause of the query in SQL View.

, CDate([forms]![form1]!ReportEndDate)-CDate([forms]![form1]!ReportStartDate)+1 AS [Total days in period], [Total days in period]-[Occupied Days] AS [Void Days]

Edited:
Uploaded a new database. Changed > and < to >= and <= in the query criteria.
.

 

[CutAndPaste]

Nearly there...

Jon,

Many thanks for the demo, I can see where I'm trying to get to now! I've altered your demo to match my field names, so far so good, but the query seems to fall over if there is no date value set in the MoveOutDate (or OccupancyEndDate as you named it).

As I'm not only interested in people who've left, but also people who are still in residence this is vital, in effect anyone who has not moved out has an effective [OccupancyEndDate] of the ReportEndDate value.

Any ideas how this can be tightend up?

THanks again, Simon

PS:
As a quick and dirty solution I'll use a make table query to write the occupancy data to a temp table and then use an update query to add the ReportEndDate to all records who have no date entered in the MoveOutDate Field and base the Occupancy Query on this temp data. Kludgy I know but it'll get the job done and I've a deadline...

 

[Jon K]

To include the null MoveOutDates, you can add an Is Null expression to the criterion of the query i.e.

WHERE (((tblHousingHistory.MoveInDate)<=forms!frmMenuMain!EndDate) And ((tblHousingHistory.MoveOutDate)>=forms!frmMenuMain!StartDate)) Or tblHousingHistory.MoveOutDate Is Null;


Note
I have also changed < to <= and > to >= in the above criteria so that records with
MoveInDate =forms!frmMenuMain!EndDate or MoveOutDate =forms!frmMenuMain!StartDate
will not be excluded.
.