In Chapter 11, "Forms/Subforms," you learned how to create a form/subform. The subforms you created were in Datasheet view, but they could have also been displayed in PivotTable view or PivotChart view. As you move from record to record in the main form, the subform's pivot table or pivot chart changes to correspond to the main record.
In the following example, you'll use the Form Wizard to create a form with a subform. You'll then design the pivot chart.
In the Database window, click Forms. Double-click Create Form by Using Wizard.
Open the drop-down list and select Table:tblOrders. Add all the fields to the Selected Fields pane.
Open the Tables/Queries drop-down list and select Table:tblOrderDetails. Add the OrderID, SellingPrice, and Quantity fields to the Selected Fields pane.
Open the Table/Queries drop-down list and select Table:tblMerchandise. Add the MerchName field to the Selected Fields pane.
By selecting MerchName from the Merchandise table instead of MerchID from the OrderDetails table, you'll be able to see the actual product name instead of the relatively meaningless merchandise ID.
Click Next. View your data by tblOrders and Form with Subform(s). Click Next.
Choose PivotChart for the layout of the subform. Click Next.
Select the Standard style and click Next.
Name the form frmOrders and the subform frmPivotOrderDetails subform.
Access creates your form, with the subform in PivotChart view (see Figure 14.22). Let's get to work on the subform.
Figure 14.22. The new form/subform in Form view. You still have much work to do on the pivot chart.
Click View to go to Design view (see Figure 14.23).
Figure 14.23. The form/subform in Design view.
Delete the unnecessary label frmPivotOrderDetails (refer to Figure 14.23).
Select the pivot chart (refer again to Figure 14.23).
Press Alt+Enter for the property sheet. Make sure the title is Subform/Subreport frmPivotChartDetails Subform.
On the Format tab, edit the Height to 4". Close the property sheet.
Click View to go to Form view. Click any empty space on the pivot chart to select it.
Be sure to select the entire chart, not just the data area. If you want to make sure you've selected the entire chart, choose View, Properties. In Select on the General tab, Chart Workspace should be selected.
Choose View, Field List.
Click Quantity and drop it onto Drop Series Fields Here (see Figure 14.24).
Figure 14.24. The pivot chart has been selected, so only the Form View toolbar is in view. The Quantity field has just been dropped onto the chart from the field list.
Click Selling Price and drop it onto Drop Data Fields Here.
Don't be misled by the title Sum of Selling Price. Because each item in any individual order in the Order Details table has only one selling price, the columns in the chart represent actual prices, not totals.
Click MerchName and drop it onto Drop Category Fields Here.
Right-click in an empty space in the chart and choose Properties. If necessary, open the Select box on the General tab and choose Chart Workspace.
On the General tab, click the Add Legend icon (see Figure 14.25).
Figure 14.25. The Properties dialog box for the Chart Workspace and the Add Legend icon.
Right-click Axis Title on the x-axis. If the Properties dialog box isn't still open, choose Properties. Click the Format tab. Edit the caption to Merchandise.
With the Properties dialog box still open, click Axis Title on the y-axis. Edit the caption to Price.
Close the property sheet. Click in any empty space to deselect the y-axis title.
Edit the Record Selector box to 16 (see Figure 14.26).
Figure 14.26. The final pivot chart, with captions for the axis titles.
The pivot chart tells you that OrderID 16 included one screen saver at a price a little above $6 and three small stuffed lions at a price a little above $11.
Close the form and save your changes.