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

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Creating a Pivot Table or Pivot Chart from a Query

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. New in Access 2002 were two new views for queries: PivotTable and PivotChart. These views are available for both your conventional Jet applications (MDB) and your Access Projects (ADP).

Creating the Query to Display in PivotTable or PivotChart View

The first thing you must do is build a query that is appropriate to display in PivotTable or PivotChart view. Queries that lend themselves to be displayed in pivot table or pivot chart view provide many ways for the user to manipulate their data. An example of such a query 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 2002. As you can see, the idea of pivot tables is to let you slice and dice the data in any way you need to at a given moment in time.

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

Create a new query in Design view.

Add the Customers, Orders, Products, Order Details, and Employees tables to the query.

Add the Country, Region, and City fields from the Customers table.

Add an expression :

SalesPerson:Employees!LastName & ", " & Employees!FirstName .

Add the OrderDate from the Orders table.

Add the ProductName from the Products table.

Add an expression :

Total:[Order Details]!UnitPrice * [Order Details]!Quantity .

We now have a query on which we can base our pivot table.

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 4.50).

Figure 4.50. When you switch to PivotTable view, the empty PivotTable view appears and the Access toolbar changes to the PivotTable toolbar.

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.
Totals 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:

Drag the Country field so that it appears as a Row field.

Drag the City field so that it appears as a second Row field to the right of the Country field.

Drag and drop the Order Date By Month field so that it appears as a Column field.

Drag and drop the Total field so that it appears as a Detail field. The resulting pivot table appears in Figure 4.51.

Figure 4.51. The pivot table that appears after dragging and dropping the Total so that it appears as a Detail field.

Displaying Summarized Data

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

Switch to Design view.

Click the Totals tool on the toolbar.

Group by all fields except the data field and any fields that you are using for a filter.

Change the Total cell for any fields you are filtering by to Where.

Change the Total cell for the data field to Sum. The resulting query appears in Figure 4.52.

Figure 4.52. The query that appears after changing the Total cell for the data field to Sum.

Run the query to verify the design (see Figure 4.53).

Figure 4.53. The underlying query after modifying it to summarize the data.

Return to PivotTable view.

To add grand totals, click any one of the Total buttons to select all three columns and then select the AutoCalc button.

With the columns still selected, right click and select Hide Details.

Observe the summarized data (see Figure 4.54).

Figure 4.54. PivotTable view after modifying the underlying query to summarize the data.

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:

Make sure that you have expanded the pivot table display to include the detail for the data on which you want to filter (see the next section, "Using Drill-Down").

Click the arrow of the field button to filter. The list contains an item for each field value (see Figure 4.55).

Figure 4.55. The list contains an item for each field value.

Click the (All) check box to deselect all fields.

Click to select the field values that you want to include in the output.

Click OK to close the list and apply the filter.

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:

Switch to Design view.

Click the Totals button to eliminate the data grouping.

Return to PivotTable view. The Total or Details Fields drop zone is empty because you changed the structure of the query.

Click the Field List button to display the PivotTable Field List.

Expand the Totals item.

Right-click the Sum of Total item and select Delete, which clears the data from the cells.

Drag the Years button outside the window to remove the columns for the years, leaving an empty No Totals column.

Click Show Details, and then drag the No Totals column outside the window. At this point, the Column Fields and Totals or Detail Fields drop zones are empty.

Drag the Total column to the Columns drop zone.

Click the Show Details button and then the Hide Details button. You will see the data expand and collapse.

Exchanging Axes

If you are viewing Year across the top and 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 4.56. You can use toolbar buttons to add legends and to modify the chart type.