Query To Get Occupancy

Question

I am having a big problem writing a query that will give the right answer to this problem.

Here's my scenario, let say you have a house, and people are allow to go in and out of the house at anytime all year round, the only requirement is that their entry and exit date/time must be noted, and each person is given a unique ID on entry, but if a person leaves then come back he/she must still use the initial ID assigned.

Now, here's my question; how do you use MS Access to query how many people occupy the house say in the whole month of January, April, June ..., basically what's the occupancy of the house within a given month, date range, time....

Thanks yall.

---

Okestra Okestra

Answer 1

The following function will identify intersecting date/time ranges:

Public Function WithinDateRange(dtmParamRangeStart As Date, _

                       dtmParamRangeEnd As Date, _

                       dtmDataRangeStart As Date, _

                       dtmDataRangeEnd As Date) As Boolean

' Accepts: date at which parameter range starts

'          date at which parameter range ends

'          date at which data range starts

'          date at which data range ends

' Returns: True if data range intersects with parameter range

'          False if whole of data range outside parameter range

   WithinDateRange = _

       dtmDataRangeStart <= dtmParamRangeEnd And _

       dtmDataRangeEnd >= dtmParamRangeStart

End Function

So, if you have OccupantID, DateIn and DateOut columns in a table say, you should be able to count the number of distinct occupants within any date/time range with:

PARAMETERS

[Enter start date:] DATETIME,

[Enter end date:] DATETIME;

SELECT

    [Enter start date:] AS DateFrom,

    [Enter end date:] AS DateTo,

    COUNT(*) AS OccupantsCount

FROM

    (SELECT DISTINCT OccupantID

      FROM YourTable

      WHERE WithinDateRange([Enter start date:], [Enter end date:], DateIn, DateOut);

---

Ken Sheridan, Stafford, England

Answer 2

Are the enter and exit date/time recorded in the same record or in different records?

Are you trying to do one time period at a time or are you trying to do multiple time periods at once?  For example, what is the occupancy for each month in the 2009.

Do people enter and exit every day or do the enter for an extended period?   Multiple entry/exits per period?

The solution to your problem depends a great deal on the structure of your data.

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County