On the Create tab, in the Queries group, click Query Wizard if you want a wizard to help you build your query, or Query Design if you want to build the query yourself. If you do not already have a query that you want to use for your PivotTable view, create a new one: For the purposes of this article, we explain how create a PivotTable view and a PivotChart view for an Access query. You can also create PivotTable and PivotChart views for Access queries, tables and forms. Items of the City field that are grouped under the custom group West Coast.Ĭlicking here displays customers based in Elgin and summary data for each customer.Ĭlicking here displays detail data for Elgin. The following illustration shows the summarized values for each city in the West Coast group.
For example, to display summarized or detail data for each city on the West Coast, click the plus sign ( +) to the left of West Coast. When a column area has more than one field, you can click the plus sign ( +) or minus sign ( -) to the left of an item to show or hide lower-level items. Note: Hiding detail data improves the performance of the PivotTable view. You can click the minus sign ( -) to the right of a salesperson or region to hide the corresponding detail data. Summary fields and data appear at the bottom of the view. These individual transactions were handled by Anne Dodsworth in the West Coast region. These detail fields exist in the underlying record source.Ī calculated detail field uses an expression based on other detail fields. The following illustration shows detail data for Anne Dodsworth. You can click the plus sign ( +) to the right of a salesperson or below a region to see individual detail records. Because the table is interactive, you can quickly change the filters, totals, and the level of detail displayed, so you can analyze your data in different ways. In the preceding illustration, you can easily compare data across different salespeople and regions. of Trans shows the number of transactions handled by a salesperson in each of the two regions. Total Trans Value shows, as a percentage value, the total value of transactions handled by a salesperson in each of the two regions. Here, the City items are grouped into two categories: West Coast and Rest of the Country. Custom group fields allow you to group items the way you want for better data analysis. The Custom Region field is a custom group field.
The Country field is filtered to show data only for the United States.
This view shows only the summarized values. The following illustration shows a query of invoice data in PivotTable view. Use a PivotTable when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. You can rotate its rows and columns to see different summaries of the source data, and you can display the details for areas of interest.
Step 2: Add and remove fields in the PivotChart viewĪ PivotTable is an interactive table that quickly combines and compares large amounts of data. Step 5: Change field captions and format dataĭifferences between PivotTable view and PivotChart view Step 4: Add calculated detail fields and total fields to the view Step 3: Add data fields to the PivotTable view Step 2: Open the query in PivotTable view This article explains the steps required to create and customize each view. You can use the PivotTable and PivotChart views when you need to either do interactive data analysis or create dynamic, interactive charts.