microsoft access reports migrated to power bi

HIPAA-compliant customized system for critical reporting and forecasting at Alameda County Mental Health Services.

When complex reports required to ensure continuity of existence for the Family Education Resource Center—an agency operated by Alameda County Mental Health Services—demanded more from a legacy database application than its original design could handle, Help4Access carried out a “rescue and replace” project. Help4Access maintained report continuity by assisting with the in-place application, while it quickly prototyped a replacement database with the flexibility to handle complex data relationships and meet extremely challenging report requirements.

Alameda County Mental Health Services (ACMHS) launched the Family Education and Resource Center (FERC) in September 2009 to assist families and care givers working with people with special needs. The program has grown rapidly across Alameda County and assists thousands of diagnosed individuals as well as their families and care providers.

As a state funded program, ACMHS also needed to forecast and manage program funding as efficiently as possible, as well as track client demographics and the program’s geographic reach at a very granular level. Reports on the services FERC has delivered are essential to avoid termination of the agency’s continued operation.

Complex reports, privacy requirements, and no internal IT staff

A diagnosed individual entered into FERC’s system needed to be linked correctly to family members, reporters, and care providers. One family member might be linked to multiple diagnosed individuals, and vice versa. If the reporter is also the individual who needs services, that must be accurately tracked. The complexity of these relationships, and correctly assembling statistics on them, was only part of the challenge. Confidentiality of data and statements became highly complex as well. One family member’s statement about a person needing services must be maintained as confidential from other family members, but made available to agency staff. Meeting HIPAA and confidentiality ethics requirements, and building statistics about contacts became a multi-layered task requiring high levels of sophistication in business analysis and programming.

FERC did not have in-house IT staff. To meet its reporting requirement, ensure security of data, and maintain consistently high levels of client service, ACMHS / FERC searched for a custom development firm and selected Help4Access in part due to its fixed price development fee.

Far from an everyday programming project

“This was an 8.5 on a scale of 10 for enterprise programming difficulty, and a 9 out of 10 for data cleansing and migration,” explained Sasha Froyland, Help4Access CEO. Help4Access first addressed the immediate need to keep reports coming from the overtaxed legacy application, to keep FERC in compliance. Next, it used Microsoft Access to quickly prototype and then rapidly build FERC’s new custom database application.

Drawing on Help4Access Bolt-On Library for speed and reduced risk

Help4Access’s Engineering team converted the ACMHS high-level business requirements, requiring hundreds of programming hours, into a high quality custom built CRM system. The team designed an easy to use customer interface, and developed highly customizable reporting capabilities that provide visually intuitive graphs for displaying the complex geographic and demographic data.

These capabilities, along with vital security features to protect data on clients, were drawn in part from the Help4Access Bolt-On Library of pretested enhancements to Microsoft Access, making them both fast and zero-risk to include. The new system allows ACMH to establish service level targets, monitor ongoing client metrics, and continuously improve the level of service provided to their clients in locations across the entire county.

Data migration and forecasting funding requirements

Help4Access repaired, cleansed, and then migrated all FERC’s data from the legacy system to the new Access database. This was a task involving major unknowable factors—the legacy database had not been designed with a correct understanding of the complex data relationships demanded by the reporting requirements.

The CRM system also provides the ability to forecast funding requirements based on historical data and quickly allocate funds to the appropriate service areas as needs arise. Using a “just-in-time” funding approach, the system now provides significant cost savings that are vital to sustaining this type of community service during ongoing budget cuts. The CRM system is used by eight staff members, three of whom have reporting privileges and two who are administrators.

Takeaway: Public sector and not-for-profit organizations may face unusually demanding report and compliance requirements that vary by state or county. There is little chance that a COTS application will be adaptable to their needs. Operating in many cases without IT staff, they depend on external resources that may lack the expertise to understand the complex data relationships required that must be designed in from the start, in order to derive required reports later.