Access Queries

You can create great interactive charts and tables using Access's own tools (PivotCharts and PivotTables), as noted in Chapter 1. However, there is a drawback to using Access PivotCharts and PivotTables — they are only interactive while working in Access. If you save a PivotChart or PivotTable as a PDF (if you have installed the Save as PDF utility) or Snapshot file, and send it to someone else, it is just an image, not an interactive chart or table. If you need to put Access data into an interactive chart or table for others to work with (even if they don't have Access installed), you can use a different approach: Export the Access data to an Excel worksheet, and then create an Excel PivotChart or PivotTable that users can manipulate as they wish.

i - • r r*.-'-^ When you create an Excel PivotChart, it is automatically created with a linked

IHHftttS&Ag PivotTab|e.

The first step in creating an Excel PivotChart is to create an Access query with the data to be charted. Excel PivotCharts are not exactly the same as Access PivotCharts; in particular, they lack the date grouping feature that automatically creates a variety of date sorts from a Date field (Year, Month, Quarter, Week). If you want to analyze data in an Excel PivotChart by month, quarter, or year, you need to do the breakdown in an Access query, before exporting the data to Excel, or create the date groups manually in Excel by using the Group command.

In Office 2007, you can create an Excel PivotChart manually, following these steps:

1. Create an Access query with the data to be charted; qryQuarterlySalesByCategory has only three fields: OrderQuarter, Category, and Price. The OrderQuarter field extracts the year and quarter from the OrderDate field, using this expression:

OrderQuarter: Year([OrderDate]) & " Q" & DatePart("q",[OrderDate])

2. Export this query to Excel using the Excel command in the Export group of the External Data tab of the Ribbon, as shown in Figure 12.8.

FIGURE 12.8

Exporting a query to Excel using a Ribbon command.

FIGURE 12.8

Exporting a query to Excel using a Ribbon command.

3. On the Export dialog, edit the worksheet name as desired, and browse for an alternate location if you don't want to store the worksheet in the default Documents folder (in Figure 12.9, I edited the worksheet name and left the folder at the default setting).

FIGURE 12.9

Editing the worksheet save name in the Export dialog.

FIGURE 12.9

Editing the worksheet save name in the Export dialog.

4. Open the newly created worksheet in Excel.

5. Click anywhere in the data range, select the Insert tab of the Ribbon, and select PivotChart from the drop-down in the Tables group, as shown in Figure 12.10.

FIGURE 12.10

Creating a PivotChart from data in an Excel worksheet.

FIGURE 12.10

Creating a PivotChart from data in an Excel worksheet.

6. The Create PivotTable with PivotChart dialog opens, as shown in Figure 12.11, with the range preselected; just click OK to create the PivotChart in another worksheet in the same workbook.

FIGURE 12.11

The Create PivotTable with PivotChart dialog.

FIGURE 12.11

The Create PivotTable with PivotChart dialog.

7. The new, blank PivotChart appears, as shown in Figure 12.12.

FIGURE 12.12

A newly created Excel PivotChart.

FIGURE 12.12

A newly created Excel PivotChart.

8. The layout of a PivotChart is different in Excel than in Access (a discrepancy Microsoft should clear up, but that is another matter). The fields from the query are listed in the PivotTable Field List in the panel on the right of the worksheet; I dragged OrderQuarter to the Axis Fields drop zone, CategoryName to the Legend Fields drop zone, and Price to the Values drop zone (Excel automatically makes it a Sum of Price). Figure 12.13 shows the plainly formatted PivotChart at this point.

FIGURE 12.13

An Excel PivotChart with fields assigned to drop zones.

FIGURE 12.13

An Excel PivotChart with fields assigned to drop zones.

9. To format the left axis number, right-click any category value in the PivotChart and select "Format Axis" from the context menu, as shown in Figure 12.14.

FIGURE 12.14

Formatting axis number.

FIGURE 12.14

Formatting axis number.

10. The Format Axis dialog opens; I formatted the left axis number for U.S. Currency by selecting Number for the Axis Option, Currency for the Category, 0 decimal places, and $ English (U.S.) for the Symbol, as shown in Figure 12.15.

FIGURE 12.15

Formatting the left axis for U.S. Currency.

FIGURE 12.15

Formatting the left axis for U.S. Currency.

When the PivotChart (or one of its components) is selected, you should see special PivotChart commands in the Design and Layout groups of the Ribbon; if you don't see them, click the PivotChart to give it the focus.

11. To give the chart a title (generally a good idea), I selected Layout 1 in the Chart

Layouts group on the Design tab of the Ribbon (in PivotChart Tools mode), as shown in Figure 12.16.

0 0

Post a comment