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

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

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Aug 5, 2003

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

By William Pearson

As in our previous lesson, we notice that the joins / relationships have been placed automatically. We review them for correctness, as always, and find them to be adequate. Next we select destination fields to determine the result dataset that the query will generate.

For each of the tables that follow, double-click the indicated fields to place it in the corresponding field of the matrix in the bottom half of the Select Query dialog.

  1. From the Customers table, select:
    • CompanyName
    • City
    • Region
  2. From the Orders table, select:
    • OrderID
    • OrderDate
  3. From the Order Details table, select:
    • UnitPrice
    • Quantity
    • Discount
  4. From the Product table, select:
    • ProductID
    • ProductName

The Select Query dialog displays the newly added tables and fields, appearing as partially shown in Illustration 4.

Illustration 4: The Select Query Dialog, Selected Tables and Fields

Now, let's run the query and examine the result set that it returns.

  1. Select Query--> Run from the main menu.

The query runs, and returns the data set, whose size is 2,155 rows, as partially displayed in Illustration 5.

Illustration 5: The Data Set Returned by Our New Query (Partial View)

Let's restrict our query to United States customers.

  1. Select View -> Design View from the main menu.

The Select Query dialog reappears.

  1. In the Customers table, double-click the Country field.

The Country field appears in the field's matrix, in the lower half of the Select Query dialog, to the right of existing fields.

  1. Uncheck the Show checkbox for the Country field.
  2. Type = USA into the Criteria box for the Country field.

The Country field appears as shown in Illustration 6.

Illustration 6: The Country Field. Set with Filter

  1. Select Query --> Run from the main menu.

We can quickly verify that only U.S. Regions (that is, States, in this table of the Northwind database) appear.

  1. Select File --> Save As.

The Save As dialog appears.

  1. Type General Customer Orders into the Save To field.
  2. Select Query from the dropdown selector for the As field.

The completed Save As dialog appears as shown in Illustration 7.

Illustration 8: The Completed Save As Dialog

  1. Click OK.

We have now created and saved the new query in our Access database. We will base our PivotTable view, and thus our PivotChart view, upon this query, to demonstrate the steps involved.

  1. Click File --> Close to close the General Customer Orders query.

We are returned to the Database window, where we can see our new query appears among the queries list.

As we have said before, a PivotTable view can be constructed for a table or a query. We created a custom query here, upon which to base a PivotTable creation, which we will, in turn, use as the basis for a PivotChart view.

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