Integrating Excel and Access

Using an Access Table for Reporting

While you can do all of this reporting without putting it into a table, I have found that by using a table to house report formats, you can make changes without touching the code. This makes it easier to develop reporting applications and allow others to maintain them. How much you put into the report formats is up to you. I suggest having fields available for font parameters, number formats, formulas, and a true/false field to allow you to hide the row. Let's look at the merits of using a table.

Assume that you wrote code that builds an income statement report with about 30 lines and several sums. At the bottom, to come up with net income, add income subtotals together and subtract expense subtotals. Of course there are underlines, double-underlines, and other formatting, as well as formulas. This report works well for a while but after five or six months in production, someone wants to add a few lines to the report to break out some expenses. If you did all of this formatting in code, you would have to edit just about every line that has to do with formatting, formulas, etc.

Now, if you have a table that holds all of the instructions, you would just need to put in the lines that you need, which would hold the formatting that you wanted for those rows. You then might have to update the formulas in the table to account for the number of new rows. I have found that it is much easier to teach someone how to update a formula in R1C1-style notation ...