Use detail fields (Detail field: A field that displays all rows, or records, from the underlying record source.) to display detail data and see what data is available to be summarized. When you add fields to the detail area, you display all of the detail records from the source data for the fields. Field names become column labels, and the detail records are displayed in rows below the column labels.

Detail fields

Detail data

You can also add a calculated detail field that uses an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) to calculate detail values.

Use row (row field: A field in the row area of PivotTable view. Items in row fields are listed down the left side of the view. Inner row fields are closest to the detail area; outer row fields are to the left of the inner row fields.) and column fields (column field: A field in the column area of PivotTable view. Items in column fields are listed across the top of a PivotTable list. Inner column fields are closest to the detail area; outer column fields are above the inner column fields.) to summarize and compare data. These fields display the unique items of data within a field down rows or across columns. The cell at each row and column intersection summarizes the data for an item.

Column field

Column field item

Row field

Row field items

Use a filter field (filter field: A field in the filter area that you can use to filter data displayed in PivotTable or PivotChart view. Filter fields perform the same functions as page fields in Microsoft Excel PivotTable reports.) to confine the view to a particular part of the available data. When you select an item in a filter field, data is displayed and calculated only for that item. For example, if you add a Region filter field, you can have the PivotTable view display and calculate data for the regions you select.

Filter field

Filter field item

Summary for the selected region

You can create new fields by adding total fields (total field: A field that summarizes data from the underlying record source. A total field might use a summary function, such as Sum or Count, or use an expression to calculate summary values.) to the PivotTable view. Total fields summarize detail data. For example, if the datasheet or form includes a Sales field that shows the amount of each sale, you could add a Sum of Sales total field to calculate the total sales, or a Count of Sales total field to calculate how many units were sold.

You can also add a calculated total field that uses an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) to calculate total values.

To eliminate data that you don't want to see or summarize in the view, you can remove fields from the PivotTable view layout. Removing a field does not remove it from the available source data. A field that you remove is no longer displayed in the PivotTable view, but it remains available in the field list for viewing.

After you add a field to a PivotTable view, you can move it to other areas. When you move a field, you change the layout of the PivotTable view, and you change the data that's displayed and the summaries that are calculated.

Filter settings are retained when you remove a field. If you remove a field and later add the field back to the layout, the same items are again hidden.

When you add, move, or remove fields in PivotTable view, Microsoft Access automatically updates the PivotChart view to reflect the change you made. For example, if you add a filter field (filter field: A field in the filter area that you can use to filter data displayed in PivotTable or PivotChart view. Filter fields perform the same functions as page fields in Microsoft Excel PivotTable reports.) to the PivotTable view, Access adds the field to the filter area in PivotChart view as well. Similarly, if you make a change in PivotChart view, Access will update the PivotTable view automatically. So, after you design the layout of one view and switch to the other view for the first time, you will not see a blank view.

Access does not synchronize formats between PivotTable and PivotChart views.

If you have made changes to the layout of a form in PivotTable or PivotChart view, Microsoft Access automatically saves your changes when you close the object. If you have made changes to the layout of a datasheet in one of these views, Access asks whether you want to save your changes before quitting.

Layout information is not stored separately for each user. For example, if another user opens a form in PivotChart view and makes changes to the layout, the next time you open the form in PivotChart or PivotTable view, you will see the layout as it was modified by the other user.

You change the layout of a PivotChart view by moving the fields to predefined drop areas (drop area: An area in PivotTable or PivotChart view where you can drop fields from the field list to display the data in the field. The labels on each drop area indicate the types of fields that you can create in the view.) within the chart workspace. To do this, drop areas must be shown in the chart.

Drop areas are displayed differently depending on the chart type. For example, a series drop area is not displayed for a pie chart because pie charts consist of only one series. If you plot multiple charts, you will see a drop area for multi-chart fields. This type of drop area is not displayed for single charts.

After you move fields to the drop areas so that the drop-area captions are covered up, you can still drag additional fields to the areas.

When you move a field to the series area, the unique items of data within the field are displayed as data series in the chart. These series are represented by colored data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that represents a single data point or value. Related data markers in a chart constitute a data series.), and their names appear in the chart legend (legend: A box that identifies the patterns or colors assigned to data series or categories in a chart.).

When you move a field to the category area, the unique items of data are displayed as categories, or related groups of data. Each category consists of one point from each data series. Category labels usually appear across the x axis of the chart, although this can vary depending on the type of chart you are using.

Data fields provide the values to be summarized in the chart. When you move a field to the data area, the values from the field are used as the data that is measured in the chart.

A filter field is similar to a page field in a Microsoft Excel PivotTable report. Filter fields allow you to confine the view to a particular part of the available data. For example, when you move a Product field to the filter area, you can have the chart display category and series values for one product at a time.

When your chart is based on data from a PivotTable list or from a database table or query, you can plot multiple charts. When you move a field to the MultiChart area, items in that field become separate charts. For example, if you move the Salesperson field to the MultiChart area, a chart is created based on data for each salesperson in that field. In the following example, the Salesperson field is in the MultiChart area, but it's filtered so it displays individual charts for Buchanan and Davolio.

When a chart contains multiple series (series field: A field that is displayed in the series area of a chart and that contains series items. A series is a group of related data points.) or category fields (category field: A field that is displayed in the category area of PivotChart view. Items in a category field appear as labels on the category axis.), the fields that are closest to the data are referred to as inner fields. The other fields are outer fields. In the following example, Year is the outer field and Salesperson is the inner field. The inner field items are displayed as salespeople's names, and the outer field items are displayed as the years 1997 and 1998.

Inner field items

Outer field items

You can expand and collapse multiple fields to show more or less information in a particular field. For example, you can collapse the outer field (Year) in the example so that the inner field items are no longer displayed.

The layout of a chart does not have to include all fields that are available from the source data. If more data is available, you can add fields to the chart. For example, if the chart summarizes sales revenue, and the source data also includes sales quantities, you might add the Quantity field as a data field to summarize both revenue and quantity of products sold. You can also remove fields from the chart layout that you no longer want to see.

Filter settings are retained when you move or remove a field. This means that when you move a series (series field: A field that is displayed in the series area of a chart and that contains series items. A series is a group of related data points.) or category field (category field: A field that is displayed in the category area of PivotChart view. Items in a category field appear as labels on the category axis.) to the filter area and back, previously hidden items are again hidden. If you remove a field and later add the field back to the layout, the same items are again hidden.