Wk 3 - Response to article 1 of 2

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 but-

ton 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 creat-

ing 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 cus-

tomize 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 select-

ed. For the field that contains the data

you want to summarize, select Value for

TECHNOLOGY

ACCESS Cross Tabulate Your Data

By Patricia Cox

5 2 S T R AT E G I C F I N A N C E I O c t o b e r 2 0 0 9

Figure 1: Selecting PivotChart or PivotTable View

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 reor-

ganized 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. SF

Patricia Cox has taught Excel and Access

to management account-

ing 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 Milwau-

kee Chapter. To send

Patricia a question to

address in the Access

column, e-mail her at

[email protected].

O c t o b e r 2 0 0 9 I S T R AT E G I C F I N A N C E 5 3

Figure 3:

Figure 2: Crosstab Query Example

Reproduced with permission of the copyright owner. Further reproduction prohibited without permission.