Most finance professionals feel that they are in their element when working with spreadsheet programs like Microsoft Office Excel 2003. You've learned some tricks, and know how to use charts, formulas, and other features to get the results you need for reports. In fact, you may be a lot more familiar with spreadsheets than you are with databases, even though database applications like Microsoft Office Access 2003 also report effectively on data.

This article is for you if you'd rather work with financial data in Excel, either because you're already familiar with its data reporting features, or you have a deadline that can't wait another minute. The good news is, Excel can import (and stay linked to) information directly from Access.

Note  If you are interested in how to report on data directly in Access, see the See Also box for more information.

Straight from the source: Access

In this example, you'll start with a simple Access database that contains asset-tracking records for three branches of Lucerne Publishing, a print company. Your management is working on budgets and cost control, and needs you to provide a report from the database that shows annual depreciation and maintenance cost totals for assets, by store, in chart form. Of course, they want this today.

Coming up with this report should  be a snap. You want to do this with bar charts, but you have to admit that you're not sure how to do that in Access (although you've been meaning to attend that training class). What now?

Start your engines

Before you begin, confirm that you know the name of the Access tables that contain your data. If you're working with a relational database, make sure that the tables you want to use are related. (If you need more information about your database to proceed, talk to your database administrator.)

After you've identified the table or tables you need, you're ready to import.

But first, quickly review your objective. You need two bar charts that compare the following information for each of the three Lucerne Publishing store locations:

• The current year's annual asset depreciation costs
• The current year's annual asset maintenance costs

Defining your goals helps you choose which fields to import from Access. In the following steps, you'll use the Microsoft Query Wizard to select the fields to import from Access to Excel. Once the data is in Excel, you'll use a Microsoft PivotTable® report and a Microsoft PivotChart® report to quickly get the results you need from that data.

Note  You can also use PivotTable or PivotChart view functionality directly in Access 2003. See the See Also box for more information.

Import data from Access to Excel

Note  In this example, you are importing data from a locally stored Access database. See your database administrator if you have questions about working with a shared or password-protected database in your organization.

1. Open a new blank workbook in Excel.
2. On the Data menu, click PivotTable and PivotChart Report.

   This opens the PivotTable and PivotChart Wizard.

3. Under Where is the data you want to analyze?, click External data source, and then click Next.
4. On the PivotTable and PivotChart Wizard – Step 2 of 3 page, click Get Data.

   This launches the Query Wizard, which helps you select and import Access data into Excel.

   Note  Microsoft Query is an optional feature for Excel. If you don't have it installed, you may be prompted to install it. For more information about importing data with Microsoft Query, see About importing data in the See Also box.

5. In the Choose Data Source dialog box, on the Databases tab, click MS Access Database. Make sure that the Use the Query Wizard to create/edit queries check box is selected, and then click OK.

   

6. In the Select Database dialog box, find your database file, click the database name in the Database Name list box, and then click OK.
7. On the Query Wizard – Choose Columns page, the tables from the database you chose are listed under Available tables and columns. Click the plus sign next to the tables that contain the data you want to use, and then double-click the fields you want to import to Excel (you can also use the arrows).

   In this example, you want to view current year maintenance costs and current year depreciation for all branches of Lucerne Publishing. This information is contained in the fields YTDMaintentanceCosts, CurrentYearDepreciation, and Store. When you are finished selecting fields, click Next.

   

8. On the Query Wizard - Filter Data page, you can filter the fields you selected. For example, if you only wanted to look at data for two stores, you could filter the Store field here. For this example, you want to see data for all stores, so you click Next.

   

9. On the Query Wizard – Sort Order page, you can choose a sort order for your data. In this example, click Next.
10. On the Query Wizard – Finish page, click Return Data to Microsoft Excel, and then click Finish.

    But wait — it's not quite over. You're finished with the Query Wizard, which helped move the data from Access, but now you have to create your PivotChart report in Excel.

    

11. On the PivotTable and PivotChart Wizard – Step 2 of 3 page, you should now see Data fields have been retrieved next to the Get Data button. Click Next.
12. On the PivotTable and PivotChart Wizard – Step 3 of 3 page, under Where do you want to put the PivotTable?, click Existing Worksheet, and then click Finish.

On solid ground: Excel

Now you're in more familiar territory: an Excel worksheet. A basic spreadsheet may just show a list of data, but a PivotTable report lets you easily move fields around to produce different views of data. Using a PivotTable report will help you view current year maintenance costs and current year depreciation by store, with little effort.

The fields you have imported from Access appear in the PivotTable Field List dialog box.

Easy year-to-date maintenance cost totals by store with a PivotTable report

1. Because you want to see data by store, drag the Store field from the PivotTable Field List dialog box to the drop area labeled Drop Row Fields Here in the PivotTable report.

   

2. This creates a list of your stores. Now, you'd like to see costs totaled by store. First, look at year-to-date maintenance costs. Drag the YTDMaintenanceCosts field to the drop area labeled Drop Data Items Here for instant totals.

   

Note  If you want to format these totals as dollars, select the cells you want to format, click Cells on the Format menu, and then click Currency on the Numbers tab of the Cell Format dialog box.

Now, get totals for current year asset depreciation costs

When you're ready to see the current year depreciation, it's a simple drag-and-drop operation:

1. Drag the Sum of YTDMainenanceCosts field off of the PivotTable report.
2. In the PivotTable Fields List dialog box, click CurrentYearDepreciation, and drag the field to the drop area labeled Drop Data Items Here for another instant report — this one showing current year depreciation cost totals by store and the total for all stores.

   

Chart your results with a click

PivotTable reports can give you instant results in chart form. A PivotChart report provides a graphical representation of the data in a PivotTable report. You can change the layout and data displayed in a PivotChart report just as you can in a PivotTable report. (For more information about PivotChart reports, see the See Also box.)

1. Click the PivotTable report, and then click Chart Wizard on the PivotTable toolbar (you can also right-click the chart, and then click PivotChart). That's it! You have a PivotChart report.

   

2. The Chart toolbar appears, enabling you to customize the chart to suit your reporting needs.

You now have one of the charts that you need: a comparison of maintenance cost totals by store. Repeat these steps with the current year asset depreciation costs PivotTable report, and you have both charts ready to include in your report.

Last but not least: It’s linked

You thought you'd washed your hands of the database — but not quite. Access stays in the picture in one very useful way: The data in your PivotTable and PivotChart reports are linked to Access, and you can update that data in Excel.

Let's say that current year maintenance costs for store 2 have changed in the database, and you need to update your chart.

1. Click the Update button on the PivotTable toolbar in your Year-to-Date Maintenance chart.
2. The data is pulled straight from Access, and both your PivotTable and PivotChart reports reflect the new totals.

From Access to Excel, for quick asset cost totals

You're finished! (And no one has to be the wiser that you have yet to take that database reporting class.) You took financial data from Access and produced the results you needed in Excel — just in time for your report.