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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Aug 4, 2003

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

By William Pearson

Defining the Data Fields within the PivotTable View

Remaining within the Queries section of the Database window, let's take the following steps to begin construction of our new PivotTable view.

  1. Click the new General Customer Orders query to select it
  2. Click Open on the Database window toolbar to run and display the query.
  3. Select View --> PivotTable View from the main menu.

After a few seconds, the blank PivotTable appears, with a floating PivotTable Field List appearing in the foreground, as depicted in Illustration 8.


Illustration 8: A Blank PivotTable View Appears

We discovered in our previous lesson how the PivotTable allows us to define the data values that we wish to occupy multiple rows, columns, pages and summaries. Let's define our view to effectively present the data set returned from our new query, in preparation for transforming that presentation to the more graphic PivotChart view.

NOTE: If the PivotTable Field List does not appear, initialize it clicking the Field List button, shown in Illustration 9, on the toolbar.


Illustration 9: The PivotTable Field List Button

  1. In the Field List, click Region to highlight it.
  2. Select Filter Area in the selector box, to the right of the Add to button (at the bottom of the Field List), as shown in Illustration 10.


Illustration 10: The PivotTable Field List with our Selection

  1. Click the Add to button.

We see the Region field appear in the Filter Area of the PivotTable (the upper left hand corner).

  1. In the Field List, click City to highlight it.
  2. Select Column Area in the selector box, to the right of the Add to button.
  3. Click the Add to button.
  4. In the Field List, click Company Name to highlight it.
  5. Select Column Area, again, in the selector box, to the right of the Add to button.
  6. Click the Add to button.

This adds the Company Name field to the right of the City field in the column area of the PivotTable.

  1. In the Field List, click Order Date to highlight it.
  2. Select Row Area in the selector box, to the right of the Add to button.
  3. Click the Add to button.
  4. In the Field List, click Order ID to highlight it.
  5. Select Detail Data in the selector box, to the right of the Add to button.
  6. Click the Add to button.
  7. Repeat steps 14 through 16 for the following fields, in the order shown:
    • Product Name
    • Quantity
    • Unit Price
    • Discount.

A small sample of the PivotTable view is partially shown in Illustration 11.


Illustration 11: The PivotTable with Our Field Assignments (Sample View)

  1. Close the Field List, as desired.

Now let's make our PivotTable more useful by adding calculated detail fields and totals.



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date