FINANCIAL REPORTING USING MICROSOFT ACCESS.

Introduction

Microsoft Access, an integral part of the Microsoft Office suite, is a powerful tool used by companies and their financial professionals to store and retrieve data. Microsoft Access is one of the most commonly used database products in the world. Countless organizations rely on Microsoft Access to store financial and operational data, while countless more use Microsoft Access' reporting tools to retrieve data from other databases.

A database, as opposed to a spreadsheet, has the ability to store a significant amount of information in tables, which can then be queried to produce information in a user-friendly report format. But Microsoft Access does much more than this. Data input forms and visual basic programming provide many new applications for Microsoft Access.

To discuss reporting, we will use as our example an accounting application manufactured by Advanced Software Development Corporation in Houma, Louisiana called UA Corporate Accounting. This product, built entirely with Microsoft tools and technology, demonstrates the potential of how Microsoft Access (and its "big brother" Microsoft SQL Server) can be used to provide a full-featured financial, operational and E-commerce solution to small and medium-size businesses. The examples here, however, can be applied to any homegrown or mass marketed Microsoft Access database file.

Although this article uses UA Corporate Accounting as its example for writing Microsoft Access reports, your data doesn't have to be in Microsoft Access in order to use its reporting functions to retrieve information. Many popular database table formats such as FoxPro, Filemaker, dBase and SQL can be "linked" into a Microsoft Access database for the design of reports.

With a simple click, Microsoft Access reports can be transferred directly into a Microsoft Excel spreadsheet or Microsoft Word document.

Technologically savvy CPAs and financial managers can use Microsoft Access to deliver real time financial and operational reports to their clients and to management.

This article shows how by following these steps:

1. Understanding what data you need on the report

2. Finding Out where the data is located in the database

3. Designing the query

4. Designing the report

5. Distributing the report

Understanding What Data You Need on the Report

As in everything in life, the devil is in the details. I have found that the most important part of reporting is planning the report. About 70% of my time is actually spent planning and thinking through the report prior to actually designing the finished product.

It is critical that you interview everyone who will be using your report. If a person will be using your report frequently, and there is some small thing about it that he or she doesn't like, then I promise that the small thing will be in that person's mind everytime the report is read. And you're the bad guy! Here are some questions you should consider:

* How often will this report be generated?

* What specific, exact data is needed on this report?

* Are there any reports now that are similar to this report?

* Is all of the data stored in a Microsoft Access table or is some of the data coming from somewhere else?

* How do you want this report filtered? (i.e., by date range, salesman, user, combination, etc)

* What totals will you need on this report?

* How shall the data be sorted?

* What kind of paper do you want this to print on? (i.e., letter or legal, portrait or landscape)

* What are the users' security permissions in this database?

* Will the user want to convert this report into a document or spreadsheet?

* Does the user want to (and have the ability to) make additional design changes to the report?

* What type of printer will this report be sent to? Does the user prefer certain fonts? Are certain font sizes too small?

After answering all of these questions, you'll need to list each of the fields of data you've determined on a piece of paper and draw out how you want the report to look. Then pass this drawing around to all of the end users to make sure that everyone likes what they see.

As mentioned before, this part of the process will take the most amount of time. But done properly, will save you the most amount of grief later on down the line.

Finding Out Where the Data is Located in the Database

Data is stored in tables. The tables are stored in the company's Microsoft Access database file which, in UA Corporate Accounting's example, is Martinson.mdb. (a sample company database) UA Corporate Accounting has hundreds of tables, so it may not be immediately clear where to find the data so that you can build a query.

One good way is to go to the data entry source. In our example, we would like to do a simple report of unbilled orders. We want basic order information on this report, such as customer name, order number, order amount and order date. In UA Corporate Accounting, all orders are entered onto the order entry screen. (Figure 1)

From this screen we see where the data is entered that will need to be retrieved by our report. To figure out which table is affected by this data entry, we must look at the design of the screen. Because UA Corporate Accounting provides us with all of the Microsoft Access source codes and modules, we can open up the order entry module. Go to the Forms tab, select this form (frmOEORders) and choose Design. When we do this, the same form appears but in design mode, which is shown in Figure 2.

By right clicking on any of these fields, and choosing Properties, we will be able to see the control source or table/query where this data is most likely going to be inserted when it's saved. Viewing the queries that update this data when it's saved will also help us determine where the data is going to be written.

From this information, we've determined that the customer ID, order number, order amount and order date are all being written to the table called "tbloeorders" in the Martinson.mdb file.

Now, we must open up this database to begin preparing our query.

Designing the Query

The query is how the raw data is retrieved for a report. Many of our clients are happy with retrieving data from a query and either printing it or outputting it directly to Microsoft Excel for analysis--they do not feel the need to make it "look pretty" in a report. Whether you decide to distribute data in a professional looking report or just by a spreadsheet, you must first do a query of the data to retrieve the information you need.

As mentioned before, the orders we've been entering into UA Corporate Accounting have been written to the main database for our sample client Martinson. To start our query, we must first open up Microsoft Access with the appropriate permissions and then browse and open up the Martinson.mdb file which looks like Figure 3.

As mentioned before, there are hundreds of tables in this database; however, your database may have less! In UA Corporate Accounting, the tables used by each module are identified by the name of the module in the beginning, so all tables beginning with tblOExxxxx represent tables that are used in the Order Entry module. We want to use the data stored in the table tblOEOrders. By highlighting this table and selecting Open, we can see the raw data in the table as in Figure 4.

This table holds all non-detailed order information for the company. In this example, we can see that if an order is not billed, it says "No" in the Billed column. This tells us that our query shouldn't retrieve everything from this table. Instead, we're going to have to define a criteria in our query to only choose those orders that have a "No" in the Billed column.

Another curveball we notice is that the customer name doesn't appear in this table; only the Customer ID. A well-written application, such as UA Corporate Accounting, wouldn't write the same data over and over again to multiple tables. Rather, it has the data in one table with an ID. Then that ID is referred to and used throughout the program. In this instance, the Customer ID refers to the customers in our table called "tblCustomers". Now we know that if we want to see the customer's name in our report, we must include the table tblCustomers in our query to retrieve this information.

It's now time to build the query. To do this, we first close this table and select the query tab in our database and choose "New" and the "Design View." A list of available tables will appear. Access is saying to us, "Which tables do you want to include in your query?" and we should double click on the tblOEOrders and tblCustomers tables to add them to our query. At this stage, our query screen should look like the screen in Figure 5.

Notice that the Customer ID field is the same in both tables and they are joined in a one-to-many relationship. Join properties are not being discussed in this article, but are very important when setting up a query.

Double click on each field (from tblCustomers - Customer Name, from tblOrders - Order #, Order Date, Amt, Billed) we want in the query and the field will drop down into the grid below.

To set the criteria so that only unbilled orders are included in this query, type "False" in the criteria box under the "Billed" field. Microsoft Access has many different criteria that you can create. For example, to choose between a data range, the criteria are between xx/xx/xx and xx/xx/xx. You can get a list of these criteria expressions under the Help menu.

Your query should now look like Figure 6.

You can now run the query by clicking on the exclamation point at the top of the screen and viewing the data retrieved, which in our example looks like Figure 7.

Closing the query, you'll be prompted to save it; call it a name like "qryopenorders." Once you've saved this you'll see your new query under the query tab.

At this point, you can always run your query and it will appear in the datasheet view as above. You can create a shortcut to this query for other users. In this datasheet, you can print out what you see or choose Tools/Office Links/Analyze with MSExcel and the datasheet view will be exported to a spreadsheet.

Getting the query to run properly is the most important thing. It's now time to make it look pretty by putting this information into a Microsoft Access report.

Designing the Report

We're still in the Martinson database. Select the Reports tab and choose New. Choose Design View and where it asks, "Choose the table or query where the object's data comes from" select our "qryopenorders" query from the drop down list. When you choose OK you'll be thrown into a report design screen.

In the detail section of the report you'll want to insert text fields that will represent Order Number, Order Date, Amount and Customer. To do this, select View/ToolBox so that the tool box appears on the screen. You then want to click on the square that shows "ab\." (The help should say this is a text box) Then, with your mouse, draw a small rectangular box in the detail section. Two boxes will appear, one saying "Text 1" (this is the field's label) and "Unbound 1." (this is the actual field) Left click on the "Text 1" box and delete it using the delete key. Position the "Unbound 1" where you want it in the detail area. Then, highlight the field, choose Edit Copy and Edit Paste and another field will appear. Position that where you want it and do this twice more so that your design screen looks like Figure 8.

Now it's time to introduce each field in the report to each field in your query. Right click on each field, one at a time, choose Properties and then under Control Source, choose the field from the query that you want. When you're done, the design should look like Figure 9.

Next, it's time to make it look "pretty." To insert a label in the Page Header section, click on the box Aa in your tool bar and draw a label over each field and then type in the label name. To make things look even better, go back to the properties for each field and make sure you like the font and format. Also, resize each field by clicking it with your mouse and expanding or contracting the size of the box so that it fits nicely. Make the detail section more compact by clicking on the bottom of the section and dragging it up to resize. Add a title by drawing a big label across the top of the page and in its properties, adjust its font and alignment.

Here's how my design looks after doing all of this. (Figure 10)

You can then choose File/Save to save the report and give it a name like "rptopen-orders." You can then preview the report by selecting the report and choosing Preview. My report looks like Figure 11.

Advanced reporting techniques are beyond the scope of this article, but rest assured that, with a little more knowledge in the ways of Microsoft Access, you can create sorts and groups for the information you want, add totals and subtotals, insert images and objects into your report, create dialog boxes to interactively ask questions and even build a data entry form that allows the user to choose options and then run the report. You can even create macros that will run the report, automatically print it out and then close the report to save additional steps.

Distributing the Report

Once the report is done, you'll see it listed under the reports tab. You can then right click on the report and choose Create Shortcut and place the shortcut on your and other users' desktops. Keep in mind that a good application, such as UA Corporate Accounting, has very strict security, so most users would need to build their shortcut under UA's security to run this report.

To use this report in another database, you can right click on the report and choose Save As/Export and choose which Microsoft Access database you want to send this report. Remember that you'll have to do the same for its underlying query that you created. This also assumes that the table structure in the other database is the same as the one for which we've created the report.

That's all to report for now!

Gene Marks, CPA is a Microsoft Certified Professional, is a principal with Demand Systems Inc. in Philadelphia, a reseller of financial software database products and services. Previously, he was a Senior Manager with KPMG in Philadelphia and a corporate controller of a publicly held biopharmaceutical company.