Business Solutions:  Computing Occupancy Rates With Access 2003 - Pseudo Code

        by James D. Duncan, CPA   June 2005

      I am still working on the questions for the survey that the agency I am helping has to fill out. The questions get harder. The question is, “What is the average occupancy rate for a given period?” This sounds simple enough. Gee, have I got caught on the simple questions here lately.
      We know that there are 18 units in the complex. We have a table containing each of the families’ entry dates and exit dates. All we need to do is compute the number of families there in January 2004, February 2004, and so on. Total up each month and divide by 18 (fixed number of units available). Average each month’s occupancy rate and we have the answer.
      My first several tries at writing a query failed. I decided to do the computations in code. First I write pseudo code to see what I need to do.

Description: Create a record for each month the family was here.

Action: Create temp file with auto id index, Family name, record for each month of occupancy in yyyy/mm format. If they were here in any part of 2 months, they would have 2 records.
 

Family Name	Month/Year
Jack Smith	01/2004	Example entry date 01/2004, exit 02/2004
Jack Smith	02/2004

Pseudo Code:
Dim the variables needed
Open record sets
Delete all the records in temp file. Need clean file each time it runs.
Determine beg/ending dates for occupancy report (01/2003-12/2003)
Open file that has family, entry date, exit date.

Read first record in Family file
  Increment year/month starting with first month through ending month for report.
    If Family entry date and exit date are in that month/year.
      Write record to temp file. Family name, date/month
    End if
  Next Increment
Next Record in Family file

Close the connections.
Run the report
Report: Temp file as source. Use report wizard. Two reports, one with detail and one with just average occupancy rate. I could do 1 report with detail and sub-report at end with just average occupancy.

2004/01
Jack Smith
Jim Jones
Etc
    Count=12 Full = 15 Occupancy rate = 80%
2004/02
Jack Smith
Jones
Albert
Carlos
    Count = xx

Total Count for months = 145 Average Occupancy rate = 80.6%

      This looks deceptively simple. I will let you know next month what traps I have laid for myself. I will follow the pseudo code and see what happens. Usually spend 80% of my time on 20% of the code. Sound familiar?
      The books on programming will tell you that the coding is the last thing you do. Design is what makes or breaks the system. There are forms for the project description, project flow, project database design and forms for reports. Actually there are many methods and many software packages to design a system. Look at Visio and you will see what I mean.
      Next month I will present the actual forms and end result. You will see that I have to modify my pseudo code because I left something out and had to add to it. But I want to show you my thought process. Or lack of it sometimes….. See you next month. If you are brave, get ahead of me and write the code or one query or two to do what I am doing in code.

James is a member of the CCOKC and an expert in Access and Excel. Click here to reach James.