Share this Page URL

Chapter 11. Analyzing Data with PivotTab... > Creating PivotTables - Pg. 381

Analyzing Data with PivotTables and PivotCharts 381 PivotTables and PivotCharts are read--only objects Although you can base a PivotTable on a form, a query, or a table, the data you see in the PivotTable (or PivotChart) can't be updated in the PivotTable. You need to open the query or table in Datasheet view or the form in Form view to update the data. Creating PivotTables You can base a PivotTable on a table, a query, or a form. In most cases, you'll work with fields from more than one table in a PivotTable, so you're likely to base a PivotTable on a query that retrieves data from related tables or on a form that's based on such a query. Displaying PivotTable data on the Web You can also display a PivotTable on a data access page. We'll describe using PivotTables with data access pages in more detail in Chapter 12, "Access and the Web." The most direct way to build a PivotTable is to open a database object and then choose PivotTable View from the View menu. (You can create a PivotChart by choosing PivotChart View.) If you want to create a form that displays data as a PivotTable, which is probably the path you should take most often, the New Form dialog box includes two options you can choose to create a PivotTable--the PivotTable Wizard and AutoForm:PivotTable. We'll use the New Form dialog box later in this section when we create a PivotTable. Verify the AllowPivotTable View or AllowPivotChart View properties If the PivotTable and PivotChart commands aren't available on the View menu when you try to open a form in one of those views, the form's AllowPivotTable View or AllowPivotChart View property might be set to No. Check the Properties dialog box for the form, and change one or both of these properties to Yes. PivotTables are similar to crosstab queries in the way they display data. Fields from the record source define the column headings, row headings, and data values displayed in the PivotTable. You can, however, arrange the way in which data is displayed more easily in a PivotTable than in a crosstab query. For example, switching a column field to a row field (or vice versa) in a PivotTable is a drag-and-drop operation that you can perform while you're viewing data. In a crosstab query, you would need to modify the query's design and then run the query again to see the data from a different perspective. See Also For more information about how to create and work with a crosstab query, see Chapter 9, "Extending Your Use of Queries." A PivotTable can have two types of row fields--an outer row field and an inner row field. An outer row field groups records in a PivotTable in a manner similar to a grouping level in a report. An inner row field displays detailed data. In addition to column, row, and data fields, a PivotTable can include one or more fields that are used to filter data. For example, you could use the Country field from the MarketingCampaigns table as a filter field in a PivotTable and then view data about a single country, a specific combination of countries, or all countries in which campaigns are organized. Some of the ways you can manage data in a PivotTable are to expand summary data to display the detailed data (or collapse detailed data), add subtotals, and view a subtotal as a percentage of the total. You can also see different levels of detail--for example, you can see the amount customers have ordered by year and also by quarter.