Integrating Excel and Access

Returning a PivotTable to Excel

Although collecting the data through the query is great, you may want to import data in a more analyzed form. Even if you are unfamiliar with PivotTables , you may find them preferable to Subtotals or any other summarizing capabilities that you currently use. When you use PivotTables, Excel makes it very easy to use their data to create a chart called a PivotChart. While you could always create a PivotTable on your own and select the name of your Data Range as the source, there may be situations when you want to see the PivotTable.

When you have gone through the previous example and are at the Import Data dialog box, click on the hyperlink for "Create a PivotTable report." You are now in Step 3 of 3 in the PivotTable Wizard (Figure 2-11). Before the PivotTable can be created, you need to click the Layout button, which brings up the screen in Figure 2-12. If you are unfamiliar with PivotTables, you can select fields to be used for Pages, Rows, Columns, and Data. For this example, let's assume that you want to see the number of shipments with freight over $100 that were shipped to each country and the total amount of the freight. To do this, click and drag Country to the Row section, and click and drag Freight to the Data section. Notice that Freight defaults to Sum; double-click on the Freight box and change it to the summarize by count option. Then click and drag the Freight field into the Data section, which defaults to Sum again. This gives you ...