A PivotTable is an interactive table that is linked to a database. Similarly, a PivotChart is an intereactive chart that is linked to a database. If you add a PivotTable or a PivotChart to a data access page and publish it on the Web, people with the appropriate software can connect to it and analyze your data in various ways.
You use PivotTables to summarize the data in a database table or query in tabular format. You can rotate the columns and rows to summarize the data from different points of view. For example, you might want to use a PivotTable view of an order details table to see the total sales for a particular product or the total sales for all products in a particular month. Similarly, you use PivotCharts to summarize data visually so that it is easy to make data comparisons at a glance.
Important
Users must have Internet Explorer version 5.0 or later, Microsoft Office 2003 Web Components, and a valid Microsoft Office 2003 license to work interactively with a PivotTable or PivotChart on a data access page. Consider who your users will be before deciding to present information in this format.
In this exercise, you will create a data access page by hand and add a PivotTable to analyze product sales.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My DocumentsMicrosoft PressAccess 2003 SBSPgsModsAnalyze folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
On the Objects bar, click Pages.
Double-click Create data access page in Design view to open a blank data access page. If Access displays a warning that earlier versions of Access cannot open the page in Design view, click OK.
If the Field List is not displayed, on the toolbar, click the Field List button.
The Field List is now displayed.
If the Toolbox is not displayed, on the toolbar, click the Toolbox button.
Size and arrange the page window, Toolbox, and Field List so that you can see them all. (If the Properties dialog box is open, you can either close, or move it to the side for now.)
In the Toolbox, click the Office PivotTable button, and then click in the upper-left corner of the blank section at the top of the page.
The PivotTable frame contains horizontal and vertical boxes labeled with the type of data they can hold: Filter Fields, Column Fields, Row Fields, and Details.
Click inside the PivotTable, and then double-click its frame to open the Properties dialog box.
Tip
Pressing
in a data access page does not open the Properties dialog box, as it does in other objects.The title of the Properties dialog box is Object: PivotTable0.
Click the Other tab, click the DataMember property, click Product Sales for 2003 from the list, and close the Properties dialog box.
The PivotTable will now be based on the Product Sales for 2003 query in the GardenCo database.
In the Field List, click the plus sign to the left of Queries, and then click the plus sign to the left of Product Sales for 2003.
You can now see all the fields in this query.
Drag the CategoryName field from the Field List to the horizontal box labeled Drop Filter Fields Here.
Click the down arrow to the right of CategoryName to see a list of all the product categories in the database. Then clear the All check box, select the Bonsai Supplies check box, and click OK.
Drag the ProductName field to the vertical box that’s labeled Drop Row Fields Here.
You see all the products in the Bonsai Supplies category.
Drag the ShippedQuarter field to the horizontal box labeled Drop Column Fields Here.
Drag the ProductSales field to the box labeled Drop Totals or Detail Fields Here.
Click the View button to switch to Page view.
Experiment with the PivotTable by selecting different categories, products, and quarters from their drop-down lists.
Save the page as
Product Sales for 2003 DAP. If Access displays a warning message that the connection string for this page uses an absolute path, click OK.Switch to Design view, in the Toolbox, click the Office Chart button, and drag a rectangle below the PivotTable and about the same width and height as the table (the grid will move down when you release the mouse button).
When you release the mouse button, you see a placeholder for the Office Chart Web component.
Click in the component to display the dialog box.
Select the Data from the following Web page item option, and then click DataSourceControl:MSODSC.
Click the Data Details tab, click the down arrow to the right of the Data member, table, view, or cube name box, and click Product Sales for 2003.
Click the Type tab, click Column in the left pane and the upper-left column chart option in the right pane, and then click the Close button to close the dialog box and see the results.
Drag CategoryName from the Field List to the Drop Filter Fields Here box. Then click the down arrow, clear the All check box, select the Bonsai Supplies check box, and click OK.
Drag ProductName to the Drop Category Fields Here box, drag ShippedQuarter to the Drop Series Fields Here box, and drag ProductSales to the Drop Data Fields Here box.
Click the View button to see the results.
Experiment with the PivotChart by selecting different categories, products, and quarters from their drop-down lists.
Save the Product Sales for 2003 DAP page.
Close the page.
CLOSE the GardenCo database, and quit Access.