24 January 2010

Real World Access (43)

One of a series of articles about where Microsoft Office Access applications have found a real-world niche.

 Doug Yudovich’s Claims Reconciliation

Sisyphus’s Questsisyphus

The process was grueling - if not physically, then mentally, filling one’s day with Sisyphean tasks:

  • Print the acceptance reports from the clearing house;
  • Print the claim run reports from our billing system;
  • Manually identify the invoice numbers between the two stacks;
  • Enter matched invoices to a spreadsheet file;
  • Scan clearing house reports to identify error messages;
  • Highlight said messages;
  • Send reports with the identified errors to the processing department;
  • The processor scans the reports, looking for the highlighted items;
  • Post the errors to the billing system.

When you were done for the day, there was tomorrow — with the claims reconciliation to, well... reconcile.

Calling the process less than optimal was an understatement.  The accuracy of the reconciliation was marginal at best — not all errors were captured, missed by either the initial reviewer or the processor. While not all invoices were reconciled as accepted and processed by the clearing houses, the claim run batches ended up being accepted in the billing system, causing an increase in the Accounts Receivable summaries.

Our billing system vendor was working on a reconciliation solution, but that was at least 18 months out.  We needed an immediate solution.

Enter stage left:  Access!

One thing in which Access excels (no pun intended) is reconciling data from various sources.  Not only can you import data into Access, you can also link directly to external data sources.

In our case, data import was the path we chose, as we were dealing with report files.

In addition to using Access’s data management engine, it is possible to automate other programs from within Access, and not only family members of the Office suite.

We reviewed the various files in the mix, and identified the approach to the data collection. Depending to the format of the source reports, a number of approaches were required — importing the file directly to Access, coding a custom import process, or writing a separate program to manipulate the date into a suitable format.

Thanks to the versatility of Access, we were able to automate that program from within Access. The automation simplified the process on a couple of fronts:

  • The user didn’t need to learn how to use a one-off product;
  • The automation guaranteed that no steps in the scrubbing process would be missed.

With the data safely tucked in Access tables, it only took a few queries and a couple of reports to get the output we needed:

  1. An itemized error report for the processor to post from;
  2. An acceptance report from the clearing house for each claim run;
  3. A list of invoices that were not accepted by the clearing house within 3 business days.

The third report was a new one.  Identifying missing claims for the processor to take care of before the Accounts Receivable gets too old.

2 months later

Sisyphus had reached the top of the mountain.

The final product was a user interface with three buttons (four when you count the ‘Exit’ button). Importing the data and generating the reports takes about 5 minutes. The reconciliation accuracy is 100% and no claim is left behind more than 3 days out.

To top it off, we freed one staff member to deal with operational needs, versus shuffling (a lot of) paper around.


Today, 5½ years later, we are finally testing one aspect of the reconciliation process in our billing system. We anticipate that the complete process will be incorporated into our billing system by 2012.

Harnessing the power of Access to manage information, and as a rapid development tool, saved us a lot of money and resources.

This application also paved the way for developing additional Access applications for the company. Some became critical to the mission of the business… but that’s a different story.


Post a Comment

Links to this post:

Create a Link

<< Home