Tips for working with Complex Access Databases used for Data Audits

Most of the development I do these days involves creating Access databases to help audit data, this involves importing data from a human resources system and from PeopleSoft, comparing the data against both systems, and finally exporting the data as EDI files for invoicing. The access databases used for these procedures generally have 30 or more queries. With this amount of queries it is hard to come back 3 months later and remember the purpose each query and the queries’ dependencies. So I have become extra careful in the names I give to the queries. Here are a couple of tips.

1) If the queries run in sequential order, precede the query name with a number such as 01, 02, 03. This will help identify when the query is run in the data audit process. My preference is to name the queries like this:
STEP-01-Import_Excel_File
STEP-02-Make_tbl_Excel_PeopleSoft_Comparison
STEP-03-Export_Results_To_Excel
With this naming convention, I can come back later and sort by the query name to get a quick idea of a query’s dependencies without having to open each query in design view. This has been invaluable in troubleshooting and improving the audit solutions.

2) If you inherit a database that uses a lot of consecutive queries and the names have already been defined and you don’t want to change the names, use the query’s Description field in the Properties Window to give the query an ordinal value. You can then sort by the query’s description field.

3) Always use the Description field in the Properties Window of the query. This can be accessed by right-clicking on the query. If you already use a naming convention similar to tip # 1, then use this field to put comments that tell you something significant or unique about the query. For example if the query connects to an external source such as another Access database, SQL Server or Excel file, here would be the perfect place to put the ODBC definition for future reference.

4) If the query creates a table, then in the description field or even in the query’s name, give the name of the table it creates.
For example: STEP-02-Make_tbl_PeopleSoft_Comparison.

5) Do not rely heavily on queries that query other queries, especially if the underlying query performs complex calculations. For example, say you need to come up with the unbilled percentage of invoices unbilled in PeopleSoft by Customer name. You probably could do this with a single query, but my advice is to first create a summary table that has all the numbers required to perform a percentage calculation by customer name. Then create a second query that performs the percentage calculation based on the summary table. This will make future troubleshooting easier and will help when someone questions your final percentage results.