Pivot Tables Using Queries

Occasionally, all the fields you need for a pivot table will be in a single table. But it's more likely that you will want to use fields from several tables. Thus, you will usually want to begin a pivot table by creating a query. In the following example, you create a query and then use it to learn more about the various features and functions of pivot tables.

Suppose that as the Nifty Lions sales manager, you want to get a sense of what kind of merchandise is selling best in each state, as well as which of your suppliers are providing this merchandise. To do this analysis, you need to know the category (for type of merchandise), the customer's state, the merchandise in each order, and who supplies which product. Table 14.1 shows the fields you need.

Table 14.1. The Fields Necessary for the Analysis

What You Need to Know



Type of merchandise



State of customer



Merchandise in order






In the Database window, choose Tools, Relationships. The Categories, Suppliers, and Merchandise tables are all related. You can join the Customers and Merchandise fields by adding linking tablesnamely, the Orders and Order Details tables. (If you have problems understanding this paragraph, review Chapter 9.)

Create the Query

To create the query, simply do the following:


In the Database window, click Queries and choose New. With Design View selected, click OK.


Add the tables in the Show Table window in the following order: tblSuppliers, tblCategories, tblMerchandise, tblOrderDetails, tblOrders, and tblCustomers.

If you want to see all the relationships, drag the Categories field list down slightly, as shown in Figure 14.6.

Figure 14.6. The Orders and Order Details fields have been added only to provide a join between the Customers and Merchandise fields.


Add the following fields from the following field lists:

  • Categories CategoryName

  • Customers CustState

  • Merchandise MerchName

  • Suppliers SuppCompanyName


Save the query as qryPivotProducts.


Click View to see your records.

With the data collected, let's create a pivot table.


Choose PivotTable View from the View button drop-down list.


Click Category Name in the field list. Drop it on Column Fields Here.


Click Product Name. Drop it on Drop Totals Or Detail Fields Here.


Click CustState. Drop it on Drop Row Fields here.


Choose PivotTable, Hide Details.


In the pivot table, right-click Category Name. Choose AutoCalc, Count.


Right-click CategoryName on the pivot table and select Hide Details.

Your pivot table should look like Figure 14.7. You can see the count of products by category and state. Note that this merely counts the number of times the product name is mentioned; it does not reflect the quantity of each product ordered.

Figure 14.7. A pivot table makes it easy to quickly add totals to the grid.


Click Company Name. Drag it all the way to the left. When you see the heavy blue line, drop it to the left of CustState.

Now you can see the merchandise ordered broken down by supplier and further broken down by customer state.


Click Company Name on the pivot table (to the left of CustState). Drag it to the left of CategoryName. When you see the heavy blue line (see Figure 14.8), drop it.

Figure 14.8. One of the many advantages of pivot tables is that you can easily switch fields to a different axis. Here the Company Name field is just about to be dropped to the left of Category Name.

This gives you counts broken down first by supplier and then by category.


Save your query and close it.


Because pivot tables are so flexible, it is tempting to throw lots of fields into the query, just in case you want to include another element. But I suggest that you decide beforehand what information you want the pivot table to show and use only the data you need for each query. You can always create another query or add fields to an existing one, if you need to.

Another Pivot Table Example

As you can see, the pivot table gives you excellent capabilities to break down data in different ways. Let's try one more example, in which you'll analyze Nifty Lions freight costs by shipper.


In the Database window, click Queries and choose New. With Design View selected, click OK.


From the Show Table window, add the tblCustomers, tblOrders, and tblShippers field lists. Close the dialog box.


From the Shippers table, add the ShipCompanyName field. From the Customers table, add the CustState field. From the Orders table, add the ShipCost and Overnight fields.


Save the query as qryPivotFreight (see Figure 14.9).

Figure 14.9. The PivotFreight query in Design view.


Click View to switch to Datasheet view and review your records.


Click the View drop-down button and choose PivotTable View.


In the field list, click Company Name. With RowArea selected at the bottom of the field list, click the Add To button next to it.

You might find this method of adding fields to the pivot table easier than dragging and dropping.


Click CustState. With RowArea selected at the bottom of the field list, click Add To.


Click Overnight?. Open the drop-down list at the bottom of the field list and select Column Area. Click Add To.


Click Ship Cost. Open the drop-down list and select Detail Area. Click Add To.


Right-click either occurrence of ShipCost in the Detail area. Select AutoCalc, Sum.

You now have shipping costs, broken down by company and then by state. The freight costs are also divided between regular and overnight orders (scroll down a bit to see the data in Figure 14.10). The pivot table also shows the total shipping costs for each shipper.

Figure 14.10. In the field list, you can use the Add To button to place fields in the various sections of the pivot table.


Save your changes and close the query.


To remove a field from the pivot table, right-click it and choose Remove.



I've been using pivot tables a bit, and there's one thing I don't get. A lot of the time I get a bunch of empty columns that say No Details (see Figure 14.11). Why is this happening? How do I stop it? Do I want to stop it?

Figure 14.11. The pivot table with empty No Details columns.


I was afraid you were going to ask me that. Let me try to explain what's going on.

In the drop-down list next to the Add To button you'll find a Detail Area selection (which I used in the previous example) and a Data Area selection (which I didn't). When you choose Detail Area, you add the actual values to the table. When you choose Data Area, you add only totals, not the actual values.

The totals you can create using Data Area are specific and limited (although common and useful). If the field has a Number or Currency data type, the totals will be sums. If the field is non-numeric (such as Text), the totals will be counts.

Suppose you want to compute any other aggregate (for example, average) or view the actual values and not just the totals. In these cases, you must first add the actual values (either by dragging and dropping the field or by using the Add, Detail Area button) and then choose the AutoCalc command.

Here's the crux of the matter: If you use Add, Data Area to add totals and then choose the Show Details command, you're going to get a bunch of columns that say No Details. Why? Because you haven't added the actual values; you've added only the totals.

I don't want to tell you to avoid the Data Area selection because it is fast and convenient. But be aware of how it works and its limitations. And remember, you can always use the AutoCalc method as an alternative.

Hands-On Microsoft Access(c) A Practical Guide to Improving Your Access Skills
Hands-On Microsoft Access: A Practical Guide to Improving Your Access Skills
ISBN: 0321245458
EAN: 2147483647
Year: 2005
Pages: 169
Authors: Bob Schneider

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net