Creating the Query for a Sample PivotTable View

Queries designed for users who are accustomed to using Excel PivotTable should offer a high degree of flexibility for slicing and dicing the data. For example, sales and marketing managers are likely to want to explore the total value of orders received each quarter by salesperson, customer, product, country, or any combination of these selection criteria. Thus, your query must supply more than the ordinary amount of data to the PivotTable.

Tip

Don't assign Caption property values to fields of tables you intend to use with queries for PivotTables and PivotCharts. You can't override the Caption property with query aliases in Access 2003.

To override the Caption property value in aggregate queries, apply a function to the field that doesn't change its values, such as Trim for text fields and Round for numeric fields. Alias the column with an AS Caption modifier. This workaround causes conventional SELECT queries to not be updatable, but most PivotTables are based on aggregate queries, which aren't updatable under any condition.

For more information the Caption property issue, see "Changing the Names of Query Column Headers," p. 343.

One of the most common forms of PivotTables displays time-series data, such as orders or sales by quarter for one or more years. To design a time-series query that supplies the underlying data for a PivotTable to display the quarterly value of orders by salesperson and country, do the following:

1. Open a new query in Design view in Northwind.mdb or your working copy of the database.

2. Add the Employees and Orders tables, and the Order Subtotals query.

3. Drag the LastName field of the Employees table to the query grid, followed in order by the ShipCountry and OrderDate fields of the Orders table, and the Subtotal field of the Order Subtotals query.

4. Add a Between #1/1/1997# And #12/31/1997# criterion to the OrderDate column to restrict the data to the last full year for which order data exists in Northwind.mdb. Clear the Show check box for this column.

5. Drag the OrderDate field from the Orders table to create a new column to the left of the existing OrderDate field. Replace the content of the Field cell of this column with 1997 Quarter: Format([OrderDate],"q") to create a calculated column to display the number of the calendar quarter in which the order was received.

6. Place the cursor at the beginning of the LastName field, and replace LastName with Name: Trim(LastName) to add an alias to the field. Replace ShipCountry with Country: Trim(ShipCountry) and add an Orders: alias to the Subtotals field. Your query design appears as shown in Figure 12.1.

   Figure 12.1. This query design provides detail data suitable for analyzing employee sales by country.

   

7. Run the query to check your work (see Figure 12.2), and save the query as qry1997OrdersByCountryPT.

   Figure 12.2. The Datasheet view of the query design of Figure 12.1 shows the altered field captions and lists the 408 orders received by Northwind Traders in 1997.