Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OccupancyCalculations (A2k SP3)

    In our extended stay database we have 3 occupancy reports.

    1. rpt OccupancySummary
    qryRentCalc0

    2. rptOccupancySumCombined
    qryOccupancySumCombined
    qryOccupancy1yrBefore
    qryOccupancySumTotal
    qryOccupancy

    3. rptoccupancyDetail
    qryRentCalc0

    One of our franchisees has just expanded their facility from 29 units to 43 units. The number of units is currently stored in tblPropertyName in a field named NumberOfRentalUnits.

    I need to have the occupancy reports reflect the change in the number of units. In this particular case the number of units changed effective 03/01/06.

    The number of rental units change very infrequently. I guess I would need to add additional fields to tblPropertyName: AdditionalRentalUnits and EffectiveDate or perhaps create a new tblNumberOfRentalUnits.

    I guess you would then have determine if there were additional rental units and if so calculate the percentages before the change date based on the old number of units and after the change on the new number of units.

    Any suggestions on the best method? I have attached a scaled down version of the DB.


    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OccupancyCalculations (A2k SP3)

    You need an extra table tblNumberOfRentalUnits with fields PropertyID (Number, Long Integer, linked to tblPropertyName), NumberOfRentalUnits (Number, Long Integer) and EffectiveFrom (Date/Time). Primary key would be the combination of PropertyID and EffectiveDate. Although it's strictly speaking derived info, you could make things a bit easier by also including an EffectiveTo field.
    You'd have to decide how to calculate the occupancy rate - probably a weighted average.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OccupancyCalculations (A2k SP3)

    If the facility opened with 29 days on 02/15/01 and added 14 rooms on 03/01/06 would the 2 records be as follows?

    Record 1 the EffectiveFrom would be 02/15/01, EffectiveTo would be 02/28/06 and the Number Of RentUnits would be 29

    Record 2 the EffectiveFrom would be 03/01/06 and the EffectiveTo would be blank, Number OfRentUnits wuld be 43

    I am confused about the Primary Key, you say the primary key would be a combination of Property ID and EffectiveDate. What is the EffectiveDate? Also, I don't know how to make the primary key a combination of two fields

    If you were running the occupancy report for a 30 day period from 02/15/06 to 03/16/06, and the occupancy days for the first 14 days was 397 and 463 for the last 16 days the occupancy would be 97.98% for the first 14 days and 85.11%for the last 16 days. Although I understand the concept of weighted averages, in this instance I do not know how to structure the formula compute the weighted average.

    Your continued assistance is greatly appreciated.

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OccupancyCalculations (A2k SP3)

    Sorry, I changed the field name while composing the reply and didn't check for consistency. I meant a primary key on the combination of PropertyID and EffectiveFrom (instead of EffectiveDate).
    To create a primary key on more than one field:
    - Open the table in design view.
    - Use Click+drag or Ctrl+Click on the field selectors on the left hand side of the window to select multiple fields (the same way you select multiple files in Windows Explorer)
    - Click the Primary Key button on the toolbar.

    The calculation for the weighted average would be

    (397 / 29 + 463 / 43) / (14 + 16)

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OccupancyCalculations (A2k SP3)

    Hans,

    Many thanks for your assistance.

    I have been unable to figure out how to access the 2 records in tblNumberOfRentalUnits so that the number of occupancy days can be determined before the change and after the change date and how to access the different number of rental units. I don

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OccupancyCalculations (A2k SP3)

    Can you explain the calculation of Occupancy day as it is now in words? (Don't try to explain all the details of the formulas, instead try to focus on the underlying idea)

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OccupancyCalculations (A2k SP3)

    Occupancy days is the number of days occupied during the report Period. For example, report period is 07-15-05 to 08-15-05 and the rent Begin date is 07-10-05 and the rent end date is 08-09-05. In this case, the rental period is for 32 days but only 26 days fall within the report period i.e. 07-15-2005 to 08-09-2005, 17 days in July and 9 days in Aug. See example 2 below. The period from 06-10-2005 to 06-15-2005, 6 days, is before the report period; therefore not included in occupancy days


    1. The entire rental period falls within the report period in which case the occupancy days are the same as the rental period (Number of Days)
    2. If the Rent Begin date is before the report date, those days have to be subtracted from the number of days
    3. Likewise if the RentEnd date is after the report end date, those days have to be subtracted from the number of days

    I hope this makes sense to you.

    Tom

  8. #8
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OccupancyCalculations (A2k SP3)

    Some addition explanation:

    The formula for OccupDays actually test for 4 conditions
    RBD = Rent Begin Date
    RED = Rent End Date
    RB = Report Begin
    RE = Report End

    Report Begin 02-01 and Report End 02-28

    1.If RBD >= RB and RBD <= RE and RED >=RE and RED <= RE, then
    #ofDays
    EXAMPLE: RentBeg 02-06 RentEnd 02-13

    2. If RBD >= RB and RBD <= RE and RED > RE then
    DaysAccumulator1

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OccupancyCalculations (A2k SP3)

    OK, that's clear. I'll try to post a reply later today.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OccupancyCalculations (A2k SP3)

    I have attached a version of your database with all existing queries etc. removed, and a series of queries to calculate occupancy rate added.

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OccupancyCalculations (A2k SP3)

    Many thanks for your assistance. I am trying to understand the 5 querues. Could you give me a brief description of your approach?

    Tom

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OccupancyCalculations (A2k SP3)

    In the first place, the names I used for the queries are not relevant, I just assigned a sequential number in the order I created them.

    qryRentCalc1 adds two fields: EffBeg and EffEnd (effective begin date and effective end date). EffBeg is the later of RentBegDate and ReportBeg, and EffEnd is the earlier of RentEndDate and ReportEnd.

    qryRentCalc2 joins qryRentCalc1 to tblNumberOfRentalIUnits, taking care to include only records for which the period from EffectiveFrom and EffectiveTo overlaps with the period from EffBeg to EffEnd.
    DatBeg is the later of EffBeg and EffectiveFrom, and DatEnd is the earlier of EffEnd and EffectiveTo.
    RepBeg is the later of EffBeg and ReportBeg, and RepEnd is the earlier of EffEnd and ReportEnd.
    OccupancyDays is calculated from DatBeg and DatEnd, Duration is calculated from RepBeg and RepEnd.

    qryRentCalc3 is a totals query based on qryRentCalc3. It sums OccupancyDays per PropertyID and RepBeg.

    qryRentCalc4 calculates the occupancy rate for each period, and the product of occupancy rate and number of days (as preparation for calculating the weighted average).

    qryRentCalc5 is a totals query again, calculating the overall occupancy rate as a weighted average.

  13. #13
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OccupancyCalculations (A2k SP3)

    Many tanks for all your assistance. I think queries 1-3 are working properly. I think there is a problem with query 4. I have run the queries on a larger body of data with the following results.

    Query 3 produces the following results

    Total
    Occ Dur Units
    745 28 29
    509 31 43

    Query 4 calculates the OccupRate
    25.6896...
    11.8372..

    To calculate the weighted average, it would seem you would add the two occupRates and divide by the total Duration
    25.6896+11.8372= 37.529 divided by 59 = 63.60 %

    I don't understand the Prod in query 4

    The result displayed in Query 4 is 1841.13%

    Tom

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OccupancyCalculations (A2k SP3)

    Could you attach a copy of the database with those data? You can remove all tables and queries not involved in this, as well as all forms, reports, macros and modules.

  15. #15
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OccupancyCalculations (A2k SP3)

    So far I have only been able to reduce it to 166 KB. I will see if I can squeeze any more out. I have compacted, compiled, zipped, copied files to new DB, all unsuccessfully.

    It appears to me that the problem is in the Prod field of query 4.

    In query 5 if you add a new summation field TotalOccup: OccupRate and then change Occupancy Rate to read [TotalOccup]/[TotalDur} that you get what I think should be the weighted average of 63.60%. Based on the information in the prior posting do you agree that the weighted average should be 63.60%?

    I have verified that 1,254 (745 + 509) is the correct total occupancy days by running the old occupancy queries.

    Tom

Page 1 of 2 12 LastLast

Posting Permissions

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