# Thread: Can anyone please tell me how I can work out occupancy rate for hostel beds

1. Registered User
Join Date
Jan 2007
Posts
11

## Unanswered: Can anyone please tell me how I can work out occupancy rate for hostel beds

Hi all
I am using Access 2003
How can I work out occupancy rate for hostel beds. The hostel has 12 beds, 8 male 4 female. We are trying to work out how often it has been fully or close to fully occupied. (The data was imported from an excel spreadsheet)
We work with people detoxing from alcohol and drugs and the max length of stay is usually 10 days.

I have a table containing nearly 5000 records going back from Dec 2006 to 1998.

Each record has three fields Date in, Date out and Gender (1 = male 2 = female) as you can see in the example data some people are there on the same night but short of doing a manual count I am stuck.

Gender Date in Date out
1 1/01/2005 11/01/2005
1 2/01/2005 10/01/2005
1 4/01/2005 11/01/2005
1 4/01/2005 10/01/2005
1 4/01/2005 10/01/2005
1 6/01/2005 7/01/2005
1 10/01/2005 18/01/2005
1 11/01/2005 18/01/2005
1 11/01/2005 18/01/2005
1 11/01/2005 17/01/2005
1 12/01/2005 17/01/2005
1 13/01/2005 20/01/2005
1 14/01/2005 17/01/2005
1 14/01/2005 24/01/2005

We have data going back to 1998 in this form – we can only guess when we have been full.

2. Registered User
Join Date
Oct 2004
Location
Melbourne, Australia
Posts
201
I assume that each of your records refers to one person (Male of female staying for X days between Date in and Date Out). Occupancy is on a per day basis, so Ii suggest you set up a VBA function which will loop through each day of the range under consideration and, for that date, counts all records where the gender is 1 or 2 and the date falls between Date In and Date Out. This will give you two numbers for each date, one of females and one of males. You can express the occupancy of the fraction that these numbers bear to the number of available beds and graph them accordingly.
My own experience in this area has some slightly gloomy overtones - calculating occupancies in aged care establsihments !

3. Registered User
Join Date
Jan 2007
Posts
11
Thanks Jim
You are right each record is for one person. So all I need to do now is:
1. find out what a VBA function is
2. learn how to write the one I need
I am on to it

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
ooh thats looks nasty.....
...laregly becasue I think the data design is flawed

what do you need this information for?
is it a one off exercise?...if so then a kludgy work around may be the best bet
is it a regular function?...ie you are going to want to do this frequently
is it for a specified date range? ie you only wnat a limited amount of dates

a way could be to write a function that looks to count how many people are in for a specified day.. using the SQL aggregate function COUNT() written cunningly this fuinction could be expanded to count the number of stays in a specified period (ie offer the option of a start date and a cut off date, if the cut off date isnt supplied use the start date)

another way could be to effectively normalise the data using a query.. or even a function and write results to a temporary table. in the temporary table you would have the persons gender and a single date of a stay.. if they stayed 10 nights then there would be 10 records

you should be able to get the same effective way using an IIF statement, possibly on a monthly template.

5. Registered User
Join Date
Jan 2007
Posts
11
Hi healdem
I would be happy to do it as a one off exercise (it is partly to show our funders what we do) but also to try and maximise the use of beds. (there is a 30% no show rate for someone booked in for detox) if we know we very realy run at full we could double book some beds.

I would like to do at leat 2006 data and prehaps 2005 in monthly blocks

Any help would be most welcome

thanks

6. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820
you should be able to do something like

datediff([date in],[date out])/[time period]

however i can't make much sense of your data how can they be checked in between 01/01 and the 11/01 yet also check in on the 02/01?

you must have data that you haven't provided that lets you ID the beds, the people or something that gives this data meaning
Last edited by m.timoney; 01-02-07 at 05:24.

7. Registered User
Join Date
Jan 2007
Posts
11
Hi m.timoney
there is a lot more data i.e an alphacode made up from the persons name. We record all the data in a closed dbase and send it to the goverment every 3 months. The gov dbase does not let us ask questions. I exported it as a c.s.v file and then input then imported it to Access. Each row you see in my example is a different person.
thanks

8. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820
hold on, light bulb goes on.

if you just want a grand total then the data doesn't need to ID the bed

SELECT Sum(DateDiff("d",[Date in],[Date Out])) AS DaysUsed, Min([Date in]) AS StartDate, Max([Date Out]) AS EndDate, DateDiff("d",[Startdate],[enddate]) AS TimePeriod, 12 AS NumberOfBeds, [Daysused]/([timeperiod]*[Numberofbeds]) AS Percentage

this is a bulky version so you can see what it's doing the cut down should look like

SELECT Sum(DateDiff("d",[Date in],[Date Out]))/(DateDiff("d",[Start Date],[End Date])*[Number Of Beds]) AS Percentage

9. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820
Ps i just realised that you might not know how to use that so heres how

create a query in design view add NO tables!, in the top corner is a button that says sql, click it, copy the above into here, change the [Your Table]; to [what ever you tables called];

10. Registered User
Join Date
Jan 2007
Posts
11
Hi m.timoney
thanks you are a star
this is what I have
Days used Start date End date Time Period Number of beds Percentage
-47572 30/03/98 28/12/06 3195 12 -1.24079290

So I guess I can make a new table with data for just one year and say just males or females.

many thanks

many thanks

11. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820
thats odd, anyidea how you ended up with negative days used?

if you use the cut down version it will prompt you for the start date and end date.

12. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820
PS you could add a where cause to the SQL something like WHERE [Gender] = 1, I've hard-code the number of beds into the query your using if you add it to a table in the database(which would be best) and then just change the hard-coded field to the place you put it.

BTW after putting the SQL in you can go back to the query builder to change things, the SQL is just the easiest way for me to get the query designed remotely

13. Registered User
Join Date
Jan 2007
Posts
11
Hello again
I am not sure why I am ending up with negative days used (but I am so much closer than I was three hours ago)
I used the cut down version start date 01/01/2005 end date 31/12/2005 number of beds 12 and I got -10.8910256410256
that nearly 11 beds were filled for most of the year?
cheers

14. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820
Can you upload the csv file? or a year of it?

i've tested it with the data you've provided and it seemed to work there and unless you've got alot of people leaving before they get there i can't think of why it's chucking out negative numbers

15. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820
for the record on the data you've provided i get the following
Code:
```DaysUsed StartDate  EndDate    TimePeriod NumberOfBeds Percentage
91	 01/01/2005 24/01/2005 23         12           0.329710144927536```
which would be 32.97%
Last edited by m.timoney; 01-02-07 at 11:51.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•