Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    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.

    Many thanks in advance.

  2. #2
    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. #3
    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

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    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. #6
    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 06:24.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    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. #8
    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
    FROM [Your Table];

    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
    FROM [Your Table];
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    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];
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    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. #11
    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.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    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
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  13. #13
    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. #14
    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
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  15. #15
    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 12:51.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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