Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Jul 1, 2003

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

By William Pearson

Adding Calculated Detail Fields and Calculated Totals

While we can bring much of the data we need into the PivotTable simply by selecting the appropriate data fields, as we have seen above, we can also present derived detail data via calculated fields. We can also present calculated totals. These calculations can make our PivotTable far more useful to information consumers, as we will see by taking the following steps:

1.      Click the Calculated Totals and Fields button (shown in Illustration 18) on the PivotTable toolbar.

Illustration 18: The Calculated Totals and Fields Button

2.      Select Create Calculated Detail Field from the dropdown options that appear.

The PivotTable Field List appears, with a new field, whose default name is Calculated, appearing at its bottom, as shown in Illustration 19. In addition, the Properties dialog for the new Calculated field appears, typically in front of the PivotTable Field List, defaulted to show the Calculation tab.

Illustration 19: The PivotTable Field List, with New Field, and the Associated Properties Box

3.      On the Calculation tab, in the text area below the Name text box, type the following:

UnitPrice * Quantity - Discount

NOTE: We can use the Insert Reference To button, after selecting each of the fields in sequence (inserting the appropriate operators), as an option for inputting our calculation. This is perhaps cumbersome, but can often help us to ensure that the fields are represented correctly; an example here would be Unit Price, which appears in the PivotTable Field List as "Unit Price" (with a space between the words). If we simply type in "Unit Price," our calculation results in an error. However, if we build the calculation using the Insert Reference To button (a form of "expression builder" approach), the correct format of "UnitPrice" (no space between words) is input - saving us the time, ultimately, in clearing the error.

4.      Type "Net Sale" into the Name text box.

The Properties box, Calculation tab, appears as shown in Illustration 20.

Illustration 20: The Properties Box, Calculation Tab


5.      Click the Format tab on the Properties dialog.

6.      In the Number format selector, select Currency.

7.      Click the Bold button in the Text Format section of the Format tab.

The Properties box, Format tab, appears as shown in Illustration 21.

Illustration 21: The Properties Box, Format Tab

8.      Return to the Calculation tab.

9.      Click the Change button at the bottom of the Calculation tab.

10.  Close the Properties box and PivotTable Field List.

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM