Occupancy Query

[Okestra007]

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.

 

[PNGBill]

You don't have to use date range if you want the whole month to be selected.

In your query you can add a field called MonthOcc eg and have this formatted as Month only using the date from the table.

This will mean your data in the query will show two date fields. One is the actual Date ie day. month etc and the other is Only the Month.
You can format it as mm or mmmm being 1, 2 or 3 Or January, February etc.
mm will give you a sort order where as mmmm wont sort well as February will be before January.

Then you can sort by Month and have all the records for that month.

If you Group by ID and include a count then you can see how many times each person came and went.

With the field included, you could leave it to the Report to do the Grouping and Sorting.

 

[Okestra007]

Thanks for the response. Think about your response, but that can't respresent the occupancy of the house, what if the same person that came in in January, came and left multiple times? Does that person count once or as many times at he/she came and left?

 

[PNGBill]

I didn't resolve your query, just offered a solution for the Month Issue.
By adding a Month only field you are able to Group your data in a Report by that field and keep all records for the month together without having to use start date and end date.

As to including all comings and goings, If you need assistance with this, what is the data you have collected in your Tables?

Post your sql query so far and state where you have a problem.

With Totals Queries you can Group records and get a Count which will give you the Number of times a person did something, provided the data was available in the first place.

 

[Simon_MT]

Isn't the issue the occupancy. Does it matter my whom!
Simon

 

[vbaInet]

You can use the Format() function as PNGBill had advised but I would suggest you use the Month() and Year() functions as this gives you more flexibility when querying.

The way to achieve what you want would be to count the DISTINCT IDs based on the filtered month and year.

Another way would be to use a Crosstab query in which you would set the WHERE clause to the current Year and then use static column headings to generate the months of the year. That is your Pivot will be something like:

PIVOT Format([Datefield], "mmm") In ("Jan", "Feb", ...etc)

You will perform a Count on the months. From this crosstab query, create a Totals Query and perform a Count on each of those month totals.

 

[Okestra007]

I am a little confused, so I thought I create a small database similar to what I am looking to get done here. I need help in putting some of what you guys are talking about into query, am a little confused. Please see attached Access Database.

 

[MarkK]

There are two things missing:
1) a clear definition of 'occupy' and 'occupancy.' Do you want a count of unique people who visited the house? A count of all visits? An average of how long all vistors stayed? A sum of how long all visitors stayed?
2) your data structure. How do you represent a visit in your tables? How do you represent a person entering the house and how do you represent a person leaving the house?
Without specifics in these two areas I don't think you've provided enough information for someone to really solve this problem.
HTH

 

[MarkK]

OK, so there is your data structure, and I don't believe it is up to the task. I would create a table like this ...

[B]tOccupancyEvent[/B]
OccupancyEventID
PersonID
DateTime
Direction

Where Direction may only be a +1 or a -1 to indicate whether the current occupancy event is an arrival or a departure for a particular person at a particular time.
I think with this structure you will realize a high degree of flexibility in querying your data.
Your existing structure fails because each record describes two discrete things, an arrival AND a departure (and a person, so three actually). A normalized database requires that any single record represent a single thing. When discrete data are combined in a single record it's like mixing milk and orange juice: you can never unmix them.
Cheers,

 

[Okestra007]

I am not creating a table, the table is already done, I am to pull information from the table using query, and its very similar to the attached database.

From my understanding, occupancy here is how many bodies occupied the house in a given month (i.e. Jan).

Assuming all we have to work with are
Unique IDs
FirstName
LastName
EntryDate
ExitDate

Thanks

 

[MarkK]

occupancy here is how many bodies occupied the house

Did you just define a word using the same word you are defining?

 

[Okestra007]

occupancy here is how many bodies occupied the house

Did you just define a word using the same word you are defining?

I think I just did,

 

[MarkK]

You could count arrivals in january 2010 using sql like ...

SELECT Count(*) AS Arrivals
FROM House
WHERE Month(EntryDate) = 1 AND Year(EntryDate) = 2010;

I'm not clear that you could consider that 'occupancy.'
Cheers,