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.
1. | In the Database window, click Forms. Double-click Create Form by Using Wizard. |
2. | Open the drop-down list and select Table:tblOrders. Add all the fields to the Selected Fields pane. |
3. | Open the Tables/Queries drop-down list and select Table:tblOrderDetails. Add the OrderID, SellingPrice, and Quantity fields to the Selected Fields pane. |
4. | 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.
5. | Click Next. View your data by tblOrders and Form with Subform(s). Click Next. |
6. | Choose PivotChart for the layout of the subform. Click Next. |
7. | Select the Standard style and click Next. |
8. | Name the form frmOrders and the subform
frmPivotOrderDetails subform. |
9. |
Access creates your form, with the subform in PivotChart view (see Figure 14.22). Let's get to work on the subform.
1. | |
2. | |
3. | |
4. | Press Alt+Enter for the property sheet. Make sure the title is Subform/Subreport frmPivotChartDetails Subform. |
5. | On the Format tab, edit the Height to 4". Close the property sheet. |
6. | 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. |
7. | Choose View, Field List. |
8. | 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. |
9. | |
10. | Click MerchName and drop it onto Drop Category Fields Here. |
11. | 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. |
12. | 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. |
13. | |
14. | With the Properties dialog box still open, click Axis Title on the y-axis. Edit the caption to
Price. |
15. | Close the property sheet. Click in any empty space to deselect the y-axis title. |
16. | 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. |
17. |
3.12.71.237