Integrating Excel and Access

Putting It Together

The best choice for reporting automation depends on your situation. I have built single report formats for income statements that are used for actual versus budget reports, trend reports, crosstab reports, and comparison reports. If I had not used a report format, I would have had to rewrite the code for every report each time there were changes. By using a report format, I can respond to changes without making any changes to code. Having said that, if you do a simple report, just want to automate it, and it is unlikely that you will make significant changes, I suggest using a template or just building it from scratch with code.

Another good concept to keep in mind is that you can have two number formats and two formulas, and then use the report format in conjunction with the created crosstab query. So, you can have results in dollars/units as well as in ratios. It is unlikely that any one solution given in this book will fully solve a particular problem. For most, the expectation is that you combine different methods to create the solution. For a project that I recently completed for a client, I had to build a database that pulled data in from text files, multiple Excel workbooks, SQL Server, and a table in the Access database. When it was finished, it automated Word to send letters, built reports in Excel, and sent a specially formatted text file to a vendor via FTP. All of the steps were automated using the techniques used in this book.

In the next chapter, ...