Award-winning author, dynamic keynote speaker, trusted advisor, & workplace tech expert 


How to Validate Data Using Microsoft Access

How I used Microsoft Access 2000 to validate a bunch of data.
Jul | 29 | 2007


Jul | 29 | 2007
This post is a little Lawson-specific, but you can generalize it to other enterprise systems.

accessI recently finished a six-month new Lawson HR 8.02 implementation at an organization with about 4,000 employees. The client was implementing Lawson HR and replacing its clunky AS400. What’s more, the company was simultaneously implementng Kronos.

Lawson Benefits went live on 1/1/03 and Payroll went live on 4/1/03. This added an entirely new level of complexity, as Access had to supplant basic Lawson functionality (such as BN145–removing flex credits for employees who have no other time records).

This article highlights how I used features in Microsoft Access 2000 to provide key audit reports, obviate the need to use vendors for custom reports and benefit election forms, and do a host of other things that were critical to successfully going live.

Comparison Queries to Map Old Data to New

This is pretty standard but absolutely critical to compare mainframe data to new Lawson data. MS Access facilitated keeping track of AS400 data and Lawson data in multiple product lines.

Comparison Queries to Spit Out Differences in Data

Once created, basic Access queries identified discrepancies such as:

  • Employees who had rate of pay X in the old system and rate of pay Y in the new system
  • Employees in one system but not the other
  • ACH account and setup discrepancies
  • Strange position dates (HR users know full well the issues with PA13.2 and these record. This issue does not disappear in version 8)
  • Employees with ‘odd birth dates’ (1-year-olds) or hire dates.
  • Employees missing critical values altogether, since HR10 had not been turned on yet to require fields
  • Employees missing flex dollars

Access to Data During System Downtime

During the implementation, the migration to different servers and different system problems left many times in which access to data through normal Lawson channels was not possible. Regularly importing data into Access allowed for an important backup. The client had purchased MS Add-ins, which facilitated importing data from Lawson to Access. ODBC was not enabled and access to tools such as dbdump and rngdbdump was not available.

Macros to Automate Emailing of Audit Reports as Attached Spreadsheets

Through some VBA and macro functionality, Access allowed these audit reports to be generated and automatically emailed to end-users.


In addition, I inserted some VBA to ‘burst’ complicated payroll distribution and overtime reports to 180 people broken down by department. Each department head now receives an email with an attached Word document containing the pay period, quarterly, and YTD information for only the employees in his/her department. Lawson’s eBroadcasting product was not available and no Lawson report produced the data in the format that the client wanted.

Reports to Generate BN Election Statements

BN245 provides employee benefit choices in a very confusing format. Again, this does not change in version 8. To replicate the client’s current form (and avoid paying another vendor to do the same), BN245 output was written to CSVs that Access imported through a macro. The end result provided a much more coherent BN election form (virtually identical to the original, which the client liked very much).

Daily HR Needs

By providing unlimited ability to link tables, complicated HR reports could be easily created and distributed during the implementation. HIPPA requirements, for example, could be addressed by a report that took a relatively short time to create. Access’ flexibility and power saved an enormous amount of time and HR users’ needs could be met.

Combining Lawson Standard Reports and Overcoming Excel Add-in Limitations

The client found Lawson’s standard reports wanting in many respects. While the Add-ins are fairly straightforward and powerful, crashes made reporting from them inconsistent. What’s more, Add-ins do not let you link every table. The client wanted certain reports that combined seemingly disparate tables. Access does not have the same limitation.

Generating SQL for Potential Conversion to Crystal

At the end of the project, the client began to consider recreating the reports in Crystal. Access facilitates this by allowing you to see queries in SQL that can be literally pasted into the Crystal Reports SQL designer.

Pivot Table Reports for Easy Auditing

For those of you not familiar with Excel Pivot Tables, you are missing out. Essentially cross tabs that allow you to slice and dice data, these were critical in identifying odd data: employees who “snuck through” Lawson benefit entry rules, specific payroll issues, and the like. Access 2000 integrates pivot tables and 2002 takes it a step further.

Creating Lawson Conversion Programs and Superior Reports

Very easily, old data was prepared for the conversion files. What’s more, Access was able to replicate the clients’ old reports, obviating the need for expensive programmers.

Summary and Conclusion: Access Is an Excellent Data-Validation Tool.

The project faced many challenges. Without the Access-based application, these challenges would have been greater and more internal and/or external resources would have been needed to go live. The client intends to use the application, at least in the interim, instead of Crystal for a whole host of reporting needs. At a bare minimum, it allows for easily auditing data and provides non-HR-end-users with key business data through email bursting.

Originally published in Lawson Guru Newsletter, June, 2003. It’s a little Lawson-specific (obviously), but the general point on data validation still holds.

Go Deeper

Receive my musings, news, and rants in your inbox as soon as they publish.


 Blog E Data E Data Issues E How to Validate Data Using Microsoft Access


Comments close 180 days after post publishes.


Blog E Data E Data Issues E How to Validate Data Using Microsoft Access

Next & Previous Posts