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 5

By William Pearson

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.

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