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.