Specialized Wizards

Access provides a number of wizards for creating quick reports . A few of them were discussed in Chapter 9. Sometimes a report isn't as simple as a hard copy of your datayou can also print labels and display charts in a report. Don't worry, though, both reports are simple to create as long as you let an Access wizard do the work.

Creating Label Reports

A label report is a little different from most reports. You can print name tags, mailing labels, file labels, or even inventory labels for storage. The uses are limited only by your imaginationif the data's in a table, you can print a label for it.

Let's use the Label Wizard to print a mailing label for each catalog in your plants database. Do the following:

1. Select Report from the Insert menu.

2. In the New Report dialog box, select the Label Wizard , select Catalogs from the drop-down control, and then click OK.

3. The wizard already knows all the dimensions for a number of labels. Most of the time, you can find your label listed among these options. For this example, select English and Sheet Feed from the Unit of Measure and Label Type sections, respectively. Then, select Avery from the Filter by Manufacturer drop-down control. Doing so limits the number of label choices.

4. Select Product Number 5095 , as shown in Figure 14.6. Access has built-in information for many common label types from various manufacturers. If you don't find a suitable label, you can click the Customize button to enter your label's dimensions. Click Next to continue.

   Figure 14.6. Choose a label by its product name, number, or dimensions.

   

5. In the next window, you can specify fonts and other font attributes. Accept the default options by clicking Next without making any changes.

6. Now you're ready to list the fields that contain the data you want to print on labels. You can also include literal characters . You'll add the catalog name and address fields as follows : Double-click Name in the Available Fields list to move it to the prototype label; then press Enter to move the insertion point to the next line. Double-click Address and press Enter . Double-click City , and then type a comma and space character. Next, double-click State , add a space, and then double-click ZIP to create the prototype shown in Figure 14.7. Click Next .

   Figure 14.7. Add labels to the Prototype label.

   

7. You can sort the labels by any field in the table. Select Name and click Next.

8. The wizard assigns a default name to the label report in the final window. You can change it, but don't. Click Finish to generate the report shown in Figure 14.8.

   Figure 14.8. The wizard generated this label report.

   

All that's left to do is to insert the label sheets and click Print!

Displaying Charts in a Report

The Chart Wizard embeds a chart in a report. The chart is actually an ActiveX controlAccess doesn't generate the chart. Access uses Microsoft Graph, which offers 20 chart formats, to generate the chart. The embedded chart lets you change its results by moving chart components to change the orientation. Or, you can modify the default calculations.

The following are two ways to add a chart to a report:

• Base a report on a query that contains the data you want to chart.

• Open an existing report and select Chart from the Insert menu to launch the Chart Wizard.

Now, let's create a simple pie chart that denotes the plants ordered from each catalog. Usually, you base a graph on a query because the wizard can't handle more than one data source or calculate the necessary values. Begin with a query that counts the number of plants ordered from each catalog by following these steps:

1. Click the Tables shortcut, select Plants in the Database window's list, choose Query from the New Object button's dropdown list, and then double-click Design View.

2. Add the CatalogName and CommonName fields to the grid, in that order.

3. Select Totals from the View menu on the Query Design toolbar.

4. Change the Group By aggregate in the CommonName column to Count , as shown in Figure 14.9. Save the query as ChartQuery and close it.

   Figure 14.9. Generating a chart from this query.

   

5. Click the Queries shortcut in the Database window and select ChartQuery.

6. Select Report from the Insert menu and double-click Chart Wizard in the New Report dialog box.

7. In the wizard's first pane, move both fields to the Selected Fields list and then click Next.

8. The next pane displays 20 chart formats. Select the Pie Chart (the first option on the last row) and click Next.

9. At this point, the wizard generates a sample chart based on the data, as shown in Figure 14.10. Don't be too concerned if the chart components don't display the correct proportions , but do check the axis controls to ensure the wizard is using the correct field(s). You want the pie chart to show the plants by catalog, so the wizard made the right choice by selecting the CountOfCommonName field. Don't change the wizard's choices; just click Next.

   Figure 14.10. You can alter the wizard's charting choices.

   

10. In the final pane, name the report Chart and click Finish without changing any other options. The results are shown in Figure 14.11. You can see with a quick glance that half the plants have come from Wildseed Farms.

    Figure 14.11. The result chart shows the relationship, by count, of the plants from each catalog.

    

Now let's add a dynamic chart to a report. By dynamic , we mean the chart will reflect the values in each record. To create this report and chart, perform the following steps:

1. Use the AutoReport: Tabular Wizard to create a quick report on the Plantings table.

2. Click View on the Print Preview toolbar to open the new report in design view.

3. Select the Photo and Online Reference controls in the Detail section, and press Delete to remove it from the report. You'll use that spot to display the chart you're about to create.

4. Select Chart from the Insert menu.

5. Using the drag-and-drop method, insert a Chart control to fill the spot where the Photo control was. When you release the mouse, the wizard displays its first pane, which wants to know where the data for the chart is going to come from. Select the Plantings table and click Next.

6. In the next pane, identify the values you want to chart. In this case, move the Bed and NumberPlanted fields to the Fields for Chart list, and click Next.

7. Select the Cylinder Column Chart the third option in the first rowand click Next.

8. Click Next without making any changes in the next pane. You don't need to alter any of the wizard's choices.

9. In the next pane, you can relate chart values to report values. The chart automatically relates the Bed fields. Add the NumberPlanted fields to the mix, as shown in Figure 14.12. Click Next.

   Figure 14.12. Relate chart values to report values.

   

10. In the final pane, accept the default name of Plantings, and click Finish.

11. Click View on the Print Preview toolbar to see the results shown in Figure 14.13. As you can see, the axis values change from record to record. As is, this particular chart might not be effective, but you can see how easily you can chart individual values.

    Figure 14.13. The axis values reflect each record.

    

The Absolute Minimum

We all use reports, and fortunately, Access has one of the best report generators on the market. Take advantage of it! In this chapter, you learned a number of ways to customize your reports, including

• Applying AutoFormat options or modifying individual report properties

• Generating label reports

• Adding charts to your reports

Note

You can click the Preview Chart button in the upper-left corner to get a more accurate look at the chart. In addition, you can change the default structure by rearranging the controls or double-clicking a control and choosing a different mathematical function.