Access.2007.VBA.Bibl..

Every development project has unique requirements; when using Access to create mailing labels

(or any other type of Word documents), you have the freedom to create tables and forms that

exactly meet your needs, storing all (and only) the information you need to select and print the

data you need on Word documents. The code used to create the shipping labels described in this

section includes several techniques that are useful for producing Word documents of various types,

such as creating filtered recordsets for working with detail records, calculating “x of y” numbers

when working with sets of records, displaying a progress meter in the status bar, and updating

tables from code.

Creating Excel PivotCharts from 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.

When you create an Excel PivotChart, it is automatically created with a linked

PivotTable.

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 cre-

ate 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;
   qryQuarterlySalesByCategoryhas 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.

NOTENOTE

Going Beyond the Basics 12