Spreadsheet, meet database - database, meet spreadsheet.

EXECUTIVE SUMMARY:

* LINKING A DATABASE and a spreadsheet allows you to perform complex data analyses far more easily than if you used only one application. In this case, we link Microsoft's Access and Excel's PivotTable.

* ALTHOUGH IT'S POSSIBLE to perform some of these functions in Access alone, the process is far more difficult.

* IN ADDITION, ACCESS can perform these functions using crosstabs, which is similar to PivotTable with one big but: Crosstabs can look at only one variable at a time; with PivotTable, however, users can look at as many variables as a spreadsheet contains with just the click of a mouse.

* ANOTHER PLUS: Most accountants feel more comfortable working with spreadsheets because they're more familiar with them.

* 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 the Excel file.

How to get two programs to work as a team.

Just 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 Access-Excel 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, page 34). 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, page 34).

When you click on the Get Data button in exhibit 2, a new screen, Choose Data Source (see exhibit 3, at left) 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, at left).

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, at left). 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, at left), 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, page 38). 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--page 38. 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.

Caveat: If at this point you cannot access the database, it's likely that your computer is missing one or two software files--either a special ODBC (open database connectivity) file that allows Excel and Access to connect or the Microsoft Query file that gives PivotTable the power to query Access. Both are installed only if, during the initial suite loading, you opted for a custom, rather than a typical, installation.

What to do: If you use the Office 2000 version and you get an error message at this point, you'll probably be asked if you want to install the missing files and then instructed to insert the Office 2000 CD disk. Follow the instructions for an automatic installation. If you use Office 97, you need to add the component manually by initiating setup again.

PivotTable Tips for Dealing With External Data

When you're working with external data in a PivotTable, consider these guides (see exhibit 10, below):

* Click on the option Save data with table layout. If you don't save a copy of the external data and you later want to analyze or customize your PivotTable, you must first click Refresh Data on the PivotTable toolbar to reattach the external data to the PivotTable.

* Clear the Enable drilldown box to prevent showing detail when you double-click a cell in the data area of a PivotTable.

* If you use an external database that requires a password, select the Save password check box to store the password as part of the query. So when you update your PivotTable, you will not need to reenter the password.

* When you click Finish in the PivotTable wizard to create a PivotTable based on external data, the query for the external data can run in either of two ways: It can run in the background so that you can continue working in Microsoft Excel while the external data is being retrieved, or it can run while you wait until retrieval of the external data is complete. To run a PivotTable query immediately, clear the Background query check box.

An Invitation

If you have a special how-to technology topic you would like the Journal to consider for inclusion in this series, or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin. His e-mail address is [e-mail].

E PHILIP HAASE, CPA, MCSD, MCT, is president of Dynamic Developers Group, a software consulting firm headquartered in Metairie, Louisiana. His e-mail address is [e-mail] and his Web site is at www.haasefam.com.