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.
What You Need to Know | Field | Table |
---|---|---|
Type of merchandise | CategoryName | Categories |
State of customer | CustState | Customers |
Merchandise in order | MerchName | Merchandise |
Supplier | SuppCompanyName | Suppliers |
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 tables—namely, the Orders and Order Details tables. (If you have problems understanding this paragraph, review Chapter 9.)
To create the query, simply do the following:
1. | In the Database window, click Queries and choose New. With Design View selected, click OK. |
2. | 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. |
3. | |
4. | Save the query as qryPivotProducts. |
5. | Click View to see your records. |
With the data collected, let's create a pivot table.
1. | Choose PivotTable View from the View button drop-down list. |
2. | Click Category Name in the field list. Drop it on Column Fields Here. |
3. | Click Product Name. Drop it on Drop Totals Or Detail Fields Here. |
4. | Click CustState. Drop it on Drop Row Fields here. |
5. | Choose PivotTable, Hide Details. |
6. | In the pivot table, right-click Category Name. Choose AutoCalc, Count. |
7. | 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. |
8. | 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. |
9. | 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. |
10. |
TIP 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. |
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.
1. | In the Database window, click Queries and choose New. With Design View selected, click OK. |
2. | From the Show Table window, add the tblCustomers, tblOrders, and tblShippers field lists. Close the dialog box. |
3. | 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. |
4. | |
5. | Click View to switch to Datasheet view and review your records. |
6. | Click the View drop-down button and choose PivotTable View. |
7. | 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. |
8. | Click CustState. With RowArea selected at the bottom of the field list, click Add To. |
9. | |
10. | Click Ship Cost. Open the drop-down list and select Detail Area. Click Add To. |
11. | 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. |
12. |
TIP To remove a field from the pivot table, right-click it and choose Remove. |
Q&A
|
18.223.33.157