Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Creating a Pivot Table or Pivot Chart from a Table

Pivot tables and pivot charts provide great ways for you to summarize detailed data stored in your Jet and SQL Server databases. Pivot tables present your data in a spreadsheet-like format, whereas pivot charts automatically render pivot table views as line, bar, or area charts. Access 2002 and Access 2003 provide two new views for tables: PivotTable and PivotChart. These views are available for both your conventional Jet applications (MDB) and your Access Projects (ADP).

Creating the Table to Display in PivotTable or PivotChart View

The first thing you must do is build a table that is appropriate to display in PivotTable or PivotChart view. Tables that lend themselves to be displayed in PivotTable or PivotChart view provide many ways for users to manipulate their data. An example of such a table is one that contains information about country, city, salesperson, sales, and date of sale. You could determine sales by city and salesperson for each month, or you could determine sales in each country for each salesperson during the year of 2002. As you can see, the idea of pivot tables is to let you slice and dice the data any way you need to at a given moment in time. A table ideal for you to display as a pivot table generally would

not be very normalized.

For our example, create the following table within the Northwind database:

Displaying the Query in PivotTable View

To switch to PivotTable view, select the PivotTable View button on the toolbar. The empty PivotTable view appears and the Access toolbar changes to the PivotTable toolbar (see Figure 2.40).

The PivotTable Field List window also appears. There are four types of fields that you will add to your pivot table. They include the following:

• Column

  fields
  Often hold date fields; generally hold information with the fewest number of data items.

• Row

  fields
  One or more fields that display data by attributes.

• Total

  or Detail

  fields
  The crosstab data itself. These are the numeric values that make up the meat of the pivot table.

• Filter

  fields
  One or more

  optional fields that restrict that data appearing in the columns, rows, or both.

To display our initial pivot table, take the following steps:

Displaying Summarized Data

Including all of the detail data could be much more detail than you need. You can alter the query design to show only summary information. Here's how it works.

Filtering Pivot Table Data

By default, Access includes all data in the pivot table. You can filter the pivot table to display only selected values for a row or column. For example, you can filter to display data for only sales in specific countries. Here's how it works:

Using Drill-Down

Generally the initial pivot table contains an excessive amount of detail. Here's how you can modify the amount of detail to show summary information only:

Exchanging Axes

If you are viewing the year across the top and the country down the side, and you decide to view year across the side and country across the top, simply drag and drop their name buttons to switch the positions in which they appear. You can also easily drag new items to the pivot table from the PivotTable list at any time, or remove them from the pivot table entirely.

Switching to PivotChart View

When you define a PivotTable view, you automatically generate a PivotChart view. You will see how evident this is by using the View tool to switch to PivotChart view. The results appear in Figure 2.44. You can use toolbar buttons to add legends and to modify the chart type.