How to get two programs to work as a team.

Spreadsheet, Meet Database
Database, Meet Spreadsheet

BY F. PHILIP HAASE

ust as two heads are better than one, so are two software applications working together. In tandem, some programs can do things that are hard, if not impossible, to do when they’re running alone. That’s why software suites—a collection of cooperative applications—can boost office productivity.

To demonstrate the power of office suites as a way to encourage you to broaden your technical skills, this article will show you how to take database information and link it to a spreadsheet’s supercalculation power so you can better interpret data.

While the process may appear difficult—and even a bit esoteric—it’s actually quite easy to do. Once you learn the technique, you’ll find it a versatile and practical tool.

The fundamental job of a database application is to warehouse information—especially diverse information—and then enable users to access and reconfigure it in nearly any customized order. Although spreadsheets can do that job, too, they can’t do it quite as well or as easily as databases. But then, a spreadsheet isn’t a database; it’s a robust, multifaceted calculator and data analyzer that incidentally can perform some database functions. So linking the two applications produces one powerful tool. Microsoft’s finance department applies this same technology to post its financial information to both its staff and to the public; see “Accounting—The Digital Way,” JofA, May99, page 99.

In this tutorial, functions of a Microsoft Access database and Excel PivotTable are paired. (For more information about using the PivotTable, see “Add Perspective to Spreadsheets,” JofA, Dec.98, page 91.)

EXAMINING FIXED ASSETS

Let’s say you keep all the data about your organization’s fixed assets in an Access database that includes, among other things, descriptions of the assets, their cost and depreciation and maintenance history. If you want to locate all the listed assets that are 75% depreciated, Access can do it, but it’s a bit complicated: First you have to write queries in Access, which is a little difficult. (Query writing will be the subject of a forthcoming Technology Workshop article.) Excel’s PivotTable not only is easier to use but it’s more adroit. In addition, financial professionals use Excel a lot more often, so they are handier with spreadsheets than with databases.

To demonstrate the real power of this AccessExcel link, we’re going to complicate the scenario. In addition to locating all the listed assets that are 75% depreciated, we now also want to see the total acquisition cost of all assets bought during the year, by month, and the depreciation taken on those assets.

Access experts may object at this point, claiming that Access can do those jobs, too. And it can, with a function called crosstabs, which is similar to a PivotTable, with one big but: Crosstabs can look at only one variable at a time; PivotTables can look at as many variables as a spreadsheet contains with just the click of a mouse.

By linking Access to Excel’s PivotTable, you not only will use a spreadsheet tool you’re probably more familiar with and that is easier to use, you now will have a powerful function that lets you manipulate raw data in multiple ways and view the information from many different perspectives—all in one operation. Even better: You can easily expand the examination with no added effort and calculate, say, the total expense for those fixed assets and even the percentage of each item to the total expense.

If that’s not enough, by linking the two files—the data file in Access to the one in Excel—any change you make in the original Access data will flow through to Excel.

GETTING UNDER WAY

To begin the exercise, open Excel and start the PivotTable wizard by clicking on Data in the toolbar and then PivotTable Report from the dropdown box (see exhibit 1). Since the goal is to link to data from an Access file, select External Data Source. That will evoke a screen asking you to identify the location of the Access data (see exhibit 2).

When you click on the Get Data button in exhibit 2, a new screen, Choose Data Source (see exhibit 3) will appear. Under the Database tab, click on MS Access 97 Database*, for that is the ODBC driver that reads the data in the PivotTable. If you have any previously saved MS Query files that you want to use, those queries will be listed under the adjacent Queries tab.

The next screen that appears selects the Access database file that contains the information you want (see exhibit 4). Highlight the name of the file and click OK, evoking a screen that invites you to choose the columns, or fields of data, that you want to include in the PivotTable (see exhibit 5). In the Available tables and columns box, you’ll see all the tables and queries (called views in some database programs) available for the file you selected. To view the columns for data in a table or query, click the + sign beside the table or query in the Available tables and columns box. By clicking on the + sign next to Assets (top image), the Available tables and columns box will show the contents of Assets (bottom image).

To see the data stored in a table or query, select the column and press the Preview now button. The data will appear in the Preview of data in selected column box. To include an available column, double-click it or use the > button to move it to the Columns in your query box. To change the order of the columns, click a column to move up or down, and then click the buttons to the right of the box. We select AssetDescription, DateAcquired and PurchasePrice (note that all the names are one word—a database convention).

FILTERING THE DATA

Click Next and the following screen invites you to filter the data to specify which rows to include in your query (see exhibit 6, at left). In this example, we want to select all assets with a purchase price greater than $2,000. To accomplish that, select a field from the Column to filter list—in this case, PurchasePrice. In the Only include rows where group, select an operation from the list on the left—in this case, is greater than and 2000. To filter the data further, click And or Or and repeat the process.

The above step isn’t always necessary because the Excel PivotTable can hold a huge amount of information. However, filtering out unneeded information—such as limiting sales for just one month instead of bringing in the whole year—not only makes a PivotTable smaller, and therefore able to calculate faster, but also simplifies building a PivotTable.

Click Next to bring up the Query Wizard—Sort Order (see exhibit 7), which lets you specify how you want to sort your data. To sort, choose a column in the Sort by box and then click the sort order that you want, Ascending (A-Z) or Descending (Z-A). You can have up to three sort levels. If you don’t wish to sort, click Next, bringing up the Query Wizard—Finish, which presents some options on the screen (see exhibit 8). The relevant one: If you want to use Microsoft Query to make further changes to your query, click on View data or edit query in Microsoft Query. If you are familiar with the Query Designer in Access, then using MS Query will come naturally. It is Access Query Designer’s little brother and has almost the same look and feel. If you have never used Access or never have written a query, then MS Query is still useful to you because it can introduce you to the world of query design in a format that is a lot less intimidating than the Access Query Designer.

When ready, click Finish. Now we have finally reached our goal: The data are in the PivotTable cache (temporary memory) and are ready for use. See the top of exhibit 9. Depending on how many data are being retrieved and the speed of your computer, this could take some time. Once the data are retrieved, the text next to the Get Data button will change to read Data fields have been retrieved. At this point, Excel takes over and treats the data just as if it had gotten the information from another Excel workbook. You then use the PivotTable Wizard (See the bottom of exhibit 9.) to create the PivotTable and perform your analyses.

PivotTables are powerful analytical tools. Users can make them more powerful if they know how to retrieve data from data sources other than another Excel spreadsheet. While this article only covers the link to one such source—a small local Access database—the same basic steps can be used if the data source is a million-record SQL database residing on an SQL server halfway around the world.