Jayhawk Area Agency on Aging Case Management database

Organization Information:

Organization Name:
Jayhawk Area Agency on Aging
City & State:
Organization Website:
Organization's Mission Statement

Jayhawk Area Agency on Aging advocates on aging issues, builds community partnerships and implements programs within Shawnee, Jefferson and Douglas counties to help seniors live independent and dignified lives.

Submission Information

Impact Essay

Jayhawk Area Agency on Aging (JAAA) is a provider of resources and a provider of services. We allocate approximately $l.3 million of state and federal funds for the provision of services to consumers. Our providers include non-profit, county health agencies and private home health agencies.

As a direct service provider JAAA provides information, assessments, case management, caregiver assistance, prescription drug assistance and health insurance counseling.

The biggest impact MS Office has had with our agency is in Quality Assurance of the case management services we provide, and reporting those services.

JAAA management needed a better method to track our 1000 case management clients being served under 6 different funding sources and grants. Each case manager had a case load of up to 100 clients coming in and out our system.


  • Each case manager (CM) tracked the units provided to clients on a pencil/paper unit sheet with client name, funding source and type of service. Besides the difficulty reading some penmanship, names such as Henry Thomas were often read as Thomas Henry and the incorrect funding source would be reported.
  • Although Case management staff was trained to update their two-week time sheet daily, most completed it when it was due, guessing the time to be charged to each of the various funding sources.
  • Monthly, Data Entry gathered CM client unit sheets to sort and bill the appropriate funding source. Every month, nearly one hundred unit sheet records would not correctly match a client under the reported funding source. Even though a few days would be spent reviewing client files correcting the reported units’ funding sources, every month units would go un-collected because no log was in the file to match the date of the units reported or a file could not be found to match reported client name.
  • Some funding sources allow travel time to be billed, some do not. CMs had to keep up with ever changing billing policies from the various funding sources

Through the generous donation of Microsoft, in 2001, Jayhawk Area Agency on Aging was able to get MS Office for all Case Management employees. Work began on an Access database to track clients and CM time. The Jayhawk Area Agency on Aging Case Management Units database has totally overhauled our case management department.

Solution to known problems:

  • CMs now turn in a daily unit reporting sheet (DUS). Unit codes are used describing the type of services the CM provided. All client logs are completed in Word and saved to a common file on Windows Server network. Supervisors check to see that logs exist to match units reported on the previous day’s unit reporting sheet.
  • Data Entry enters units in an Access database. If the DUS shows a funding source that does not match the assigned funding source, Access catches the discrepancy and corrections are made immediately.
  • Bi-weekly time sheets are created from the DUS input, accurately reporting CM time to the various funding sources
  • Mileage reimbursement reports are created from the DUS input, accurately reporting mileage expense to the various funding sources.
  • Billing reports are created from the DUS input for each of the funding sources. One funding source accepts our billing electronically in table format. Billable unit codes are filtered according to the policy of the different funding sources.

As the power of this database became apparent, management has added multiple reports and queries:

  • Quality Assurance can spot check the previous days client visits for follow-up
  • Annually, client files are reviewed verifying that paper logs have been filed in each client’s file matching the units reported. Often, a paper log was in the file that was not reported on the CM’s daily report. These units are now captured and included on the next billing report
  • At each CM’s annual employee review, a snap shot of their year is available showing the percentage of their time that was spent working with clients, giving supervisors and employees a data driven measure of performance
  • When a major funding source changed their policy on what was billable, we knew immediately the financial impact on the agency
  • In case of disaster, extremely vulnerable clients are flagged who will need special attention. For example, Access has allowed us to search certain areas during a long power outage, to reach those who must have electricity for their oxygen.
  • Clients with duplicate names are easily identified
  • A monthly recap loads all billable and non-billable time, verifying that all client specific time is appropriately reported
  • As clients await eligibility, their units can ‘sit’ for a few months. These pending clients are regularly reviewed so that units are not lost past the acceptable billing window time
  • Demographic statistics are easy to access by county
  • Mailing lists can be manipulated by county, funding source, etc.

The versatility of MSOffice and Windows Server is magnificent. As funding sources and billable policies change, our database can change with us. This database has had such an impact on our agency, that other area agencies in our state have asked to copy it.

Submission Category
Optimize Mission Delivery
Project Image
jaaa GOOD logo.jpg