MS Access for the Business Environment: Create a PivotTable View in Access - Page 5

July 1, 2003

The new Net Sale calculated detail field appears within our PivotTable, a view of which is partially depicted in Illustration 22.

Click for larger image

Illustration 22: The Net Sale Calculated Field Appears (Partial View - Compressed)

11.  Click and drag the label of the Net Sale calculated field to the right of the Discount field.

12.  With the Net Sale field still selected, click AutoCalc on the PivotTable toolbar, and then click Sum.

The AutoCalc button, complete with its cascaded menu, is shown in Illustration 23.

Illustration 23: The AutoCalc Button with Cascaded Menu

13.  Click AutoCalc again, this time selecting Count to add another total field to our view.

When we use AutoCalc, we are creating calculated fields, just as we did with our "expression editor" approach for Net Sale. AutoCalc simply creates the fields with common aggregate functions that are built in, represented by the selections in the menu shown above.

Our PivotTable now appears as partially shown in Illustration 24.

Illustration 24: Partial PivotTable View, with Totals Fields

In addition to seeing our new calculated totals in the PivotTable view, we can see that they have been added to our PivotTable Field List, where we can easily access property setpoints for formatting and other settings.

14.  Click the PivotTable Field List button (see Illustration 15 for a view of the button) to resurrect the Field List.

The Field List appears, and displays our new calculated totals under Totals atop the list, as shown in Illustration 25.

Illustration 25: PivotTable Field List, Totals Expanded to Show New Calculated Totals

We have successfully created calculated detail fields and calculated totals. Now let's take a brief look at organizing and formatting our presentation a bit, to enhance its value to information consumers.