Printer Friendly

Cross tabulate your data.

There's a powerful summary feature in Excel called a pivot table. This feature cross tabulates data using column and row categories. The tabulation can be done for totals, counts, or averages. This is such a useful feature of Excel that people often ask me how to do pivot tables in Access. There are two ways that you can do similar cross tabulation work with your data in Access. You can create a pivot table view for a select query, or you can create a crosstab query. You also can create a pivot chart view of the select query if you want to graph your data.

Pivot Table Query View

Create a select query, and include the fields that you want to use for the columns and row headings and the value field to summarize for your pivot. Then select PivotTable View from the View button on the Design ribbon (see Figure 1).


Drag your row, column, and value fields to the appropriate places, similar to creating a pivot table in Excel. You can print a pivot table or e-mail it as an attachment in various file formats. You can also export the pivot table data in various file formats such as .XLS or .TXT.

Pivot Chart Query View

You can also turn your data into a pivot chart by creating a select query that includes the fields you want to chart. Change the view to PivotChart View on the Design ribbon. Then, as when creating a pivot chart in Excel, drag the filter, data, category, and series fields to the appropriate places.

Use the tools on the Design ribbon to customize the chart. Right-click areas of the chart and choose Properties to customize the titles, scale, or font. Right-click and choose Change Chart Type to change the chart to a pie, line, bar chart, etc. You can easily print a pivot chart as well as e-mail it, though the underlying pivot table data will be sent, not the actual chart.

Crosstab Queries

Another way to cross tabulate your data is to design a crosstab query. To start, create a new query, and add the tables with the data you want to summarize. In the Query Type section of the Design ribbon, click Crosstab. This will add two new lines to the Query by Example design grid: Total and Crosstab will appear between the Table and Sort lines.

To indicate the row and column headings, go to the appropriate field in the Query by Example grid and select Group By on the Title line and Row Heading, or Column Heading, in the Crosstab line. Often, column headings are years or other time frames. Usually there's only one column heading selected. For the field that contains the data you want to summarize, select Value for the Crosstab line and Sum or Count (or another appropriate choice) for the Total line. Figure 2 is an example that will cross tabulate data for extended sales by State and Product Category. When you're ready to see the cross tabulated data, run the query.


Hints and Cautions

Here are several things to keep in mind when using these processes:

You can filter or sort row or column contents by clicking the dropdowns on the screen (see Figure 3).


If there is a null value in a column heading field, the query will give you an error message when you run it. To resolve this, make sure all the values for the fields are entered or add Is Not Null to the criteria line for this field.

If you create a report from a crosstab query and the column headings later change, you may have to adjust the report as time goes by to reflect these changes. The report won't update the fields automatically when they change.

When you save a query and reopen it, you may find that Access has reorganized your fields a bit. This is because when Access saves a query, it's actually saving the SQL code behind the query. When the query is reopened in design view, Access rebuilds the query from the SQL code. If you want to see what the SQL code looks like, you can select SQL View from the View tab on the Design ribbon.

Finally, you can export a report as a snapshot to save a copy of it outside of Access.

Next month we'll look at make-table queries and discuss when to use them and when to avoid them.

Patricia Cox has taught Excel and Access to management accounting students at Alverno College in Milwaukee, Wisc., and has consulted with local area businesses to create database reporting systems since 1998. She is a member of IMA's Greater Milwaukee Chapter. To send Patricia a question to address in the Access column, e-mail her at
COPYRIGHT 2009 Institute of Management Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2009 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:ACCESS
Author:Cox, Patricia
Publication:Strategic Finance
Geographic Code:1USA
Date:Oct 1, 2009
Previous Article:Performing all lookups in a single formula.
Next Article:Tools of the trade.

Terms of use | Privacy policy | Copyright © 2020 Farlex, Inc. | Feedback | For webmasters