Building Reports

Building Reports

Computers are convenientbut not for everything. Suppose you want to take your list of plants to the nursery to make sure that you know which varieties you already have when you're shopping? Or you might want to put your plant pictures into a physical photo album, as well as storing them on your computer. If you start treating your garden as a commercial activity, you'll also need to keep paper records to satisfy your accountant .

For all these reasons, you need to be able to get information from your database and send it to a printer instead of to the screen. Fortunately, Access includes the perfect object for this: It's called a report . In this chapter, you'll learn how to develop your own reports for those times when having the data onscreen just isn't enough.

Note

You can download the Chapter 9.mdb sample file, which is inclusive of all the examples in this chapter, from http://www.quepublishing.com/. If you want to follow along with our examples, download Chapter 8.mdb .

Why Not Just Print Forms?

While you were working with forms, you might have noticed the Print and Print Preview commands on the File menu. Maybe you could just use those commands to print your forms and save yourself the trouble of building additional objects? Well, you couldbut you're not likely to be happy with the results. Here's how to take a look and judge for yourself:

1. Launch Access and load the Plants database.

2. Use the AutoForm: Tabular wizard to create a form named Catalogs, by selecting the Catalogs table in the Database window, choosing Form from the Insert command, and then double-clicking AutoForm: Tabular in the New Form dialog box.

3. Select File, Print Preview . This opens a print preview window showing the form.

4. Move the mouse cursor over the print preview window, so that it displays a magnifying glass cursor, and click the mouse button. This zooms in on the print preview window, as shown in Figure 9.1.

   Figure 9.1. Printing an Access form.

   

Not very inspiring , is it? Forms are great for onscreen display, but printing a vast expanse of gray with small bits of embedded information is not usually what you want. For printed output that's actually optimized for a printer, you need to use a report.

Caution

Before you try to print or preview any objects in Access, make sure you've installed a printer in your Windows settings. Otherwise, things will look terrible!

Creating Simple Reports

Access lets you easily create reports. As always, there's more than one way to perform this particular task. In this section, you'll learn about three ways that you can create reports in your Access database:

• Using AutoReports

• Using a Report Wizard

• Using report design view

These methods of building reports offer you a choice between the amount of work you need to do and the amount of customization you can perform: Report design view is the most flexible of the three, but it's also the most complex. For many of your reporting needs, a simple AutoReport or a report generated by the Report Wizard should be more than adequate.

Note

Access Print Preview is practically identical to the way things look when printed. We'll be using print preview in this chapter because there's no easy way to take a screen shot of the printed results!

Building an AutoReport

The easiest way to build a report in Access is to use the AutoReport wizard. You can think of AutoReport as a wizard that doesn't ask you any questions; it just makes the best guess that it can and then builds it. Here's how to create an AutoReport from the Catalogs table:

1. Select the Tables shortcut in the Database window.

2. Select the Catalogs table.

3. Select Insert, AutoReport from the Access menu.

4. Select File, Save and save the report as CatalogsAuto .

That's it! Access will design and open the report shown in Figure 9.2. The default AutoReport still wastes a lot of space, but it doesn't add unnecessary graphical clutter to the printed page.

Figure 9.2. An AutoReport based on the Catalogs table.

You'll note that the catalogs on this report are sorted in the same order in which they're sorted in the underlying tablethat is, by the Name field, which is the primary key of the table. What if you want the report in a different order? One answer is that you can base a report on a query, which, as you know, can sort records in any order. For example, you might build an AutoReport that shows the catalogs sorted by specialty. To do so, follow these steps:

1. Select the Catalogs table in the Database window.

2. Click the drop-down arrow next to the New Object button on the Access toolbar, and select Query from the list.

3. In the New Query dialog box, select Design View and click OK.

4. In the Query1 design window, double-click the asterisk at the top of the list of fields in the Catalogs table. This is a shortcut for adding all the fields from the table to the query.

5. Drag the Specialty field from the field list to the query grid. Set the Sort for this field to Ascending and uncheck the Show box.

6. Select File, Save and save the new query as CatalogsBySpecialty , and close the query.

7. Select the Queries shortcut in the Database window.

8. Select the new CatalogsBySpecialty query.

9. Select Insert, Report.

10. In the New Report dialog box, select AutoReport: Tabular , as shown in Figure 9.3. Click OK.

    Figure 9.3. Creating a tabular AutoReport from a query.

    

11. Select File, Save and save the new report as CatalogsBySpecialty . Figure 9.4 shows the new report.

    Figure 9.4. A tabular AutoReport based on a query.

    

As you can see, two styles of AutoReport are available in Access. The tabular AutoReport makes better use of space than the columnar AutoReport (which you saw in Figure 9.2). In its column headings and report title it also gives you the first hint of customization that you can bring to the printed page with Access reports.

Tip

It's okay to have two different objects with the same name in an Access database, as long as they're different types of objects. For example, a query and a report can share a name, but you can't have two queries with the same name.

Using the Report Wizard

If you refer to Figure 9.3, you'll see that Access actually offers the following six ways to create a report:

• Design view Although it's first on the list, it is actually the most complex option. We'll introduce you to design view later in this chapter and then cover it more extensively in Chapter 14, "Dressing Up Your Reports."

• Report Wizard Offers you a flexible wizard-based way to create reports based on one or more tables. We'll cover this option next.

• AutoReport: Columnar Creates a columnar report, in which all the fields are laid out in a single column.

• AutoReport: Tabular Creates a tabular report, in which fields are laid out across the printed page.

• Chart Wizard Creates a report containing a chart. You'll learn about this wizard in Chapter 14.

• Label Wizard Creates a report you can use to print mailing labels. You'll learn about this wizard in Chapter 14.

In the next section of the chapter, we'll show you several ways to use the Report Wizard. You can base a report on a table, a query, or even more than one object at the same time.

Using the Report Wizard with a Table

To get started with the Report Wizard, you can build another report based on the Plants table. Here's how:

1. Select the Reports shortcut in the Database window.

2. Click the New button on the Database window toolbar to open the New Report dialog box.

3. Select the Report Wizard option in the list of report types. Then select the Plants table from the drop-down list of all the tables and queries in the database. Click OK.

4. On the first panel of the Report Wizard, shown in Figure 9.5, select the fields you want to see on your report. For this example, select the CommonName , LatinName , and Picture fields. Click Next.

   Figure 9.5. Selecting fields in the Report Wizard.

   

5. The second panel of the Report Wizard lets you specify grouping options for the report. You'll see how grouping works a bit later in the chapter. For now, just click Next to proceed.

6. The third panel of the Report Wizard enables you to sort your report by up to four fields. In this case, choose to sort by CommonName, as shown in Figure 9.6. Click Next.

   Figure 9.6. Choosing a sort order for a new report.

   

7. The fourth panel of the Report Wizard enables you to choose layout options for the report. In this case, we've chosen a tabular layout and specified landscape orientation for the paper in the printer. Figure 9.7 shows the options on this panel. Notice that the sample report to the left of the options changes to reflect your choices. Click Next after selecting layout options.

   Figure 9.7. Layout options for a report.

   

8. The fifth panel of the Report Wizard enables you to select a style for the report. Styles let you choose fonts and colors for the report. For this example, select the Casual style and watch the sample report to the left change to reflect that style. Click Next to continue.

9. The final panel of the report lets you assign a name to the report and then decide what to do with it next. Name the report Plants and click Finish to open the report in print preview mode. Figure 9.8 shows the finished report, maximized inside the Access window.

   Figure 9.8. A new report created by the Report Wizard.

   

The Report Wizard seems to have made a strange decision about pictures on this report; they take up a lot of space, but the pictures themselves are tiny. We'll show you what happened and how to fix it a bit later in this chapter, in the section "A Peek at Report Design View."

Using the Report Wizard with a Query

You can also use the Report Wizard to create a report based on a query. You have the same options that you do when you base the report on a table. Here's how you might use the Report Wizard to base a report on the CatalogsBySpecialty query. You'll recall that this query retrieves all the catalogs in the database, sorted by their specialty. Do the following:

1. Select the Reports shortcut in of the Database window.

2. Double-click the Create Report by Using Wizard shortcut.

3. On the first panel of the Report Wizard, select the CatalogsBySpecialty query from the drop-down list of tables and queries. Select all the fields in the query and click Next . Remember, you can use the >> button as a way to select all the available fields in a single click.

4. On the second panel of the Report Wizard, don't select any grouping levels. Click Next.

5. On the third panel of the Report Wizard, choose to sort the report in descending order by state. Figure 9.9 shows these settings. You can click the Ascending button next to the sort field to change from an ascending sort to a descending sort. Click Next.

   Figure 9.9. Choose a descending sort by state.

   

6. Create a tabular report in portrait orientation and click Next.

7. On the fifth panel of the Report Wizard, select the Compact style. Then click Next.

8. Name the report CatalogsByStateDescending and click Finish . Figure 9.10 shows the completed report.

   Figure 9.10. A report based on a query.

   

Selecting the State field on which to sort the report, as you did in step 5, has two consequences. First, the Report Wizard assumes that this field is important to you and therefore moves it all the way to the left on the finished report. Second, the sort you specified in the Report Wizard takes precedence over the original sort order that was specified by the query.

Using the Report Wizard with More Than One Table

For the most information in a single report, you can base your report on more than one table. For example, suppose you'd like to see a report of plants and the catalogs they came from? Here's how to do that:

1. Select the Reports shortcut in the Database window.

2. Double-click the Create Report by Using Wizard shortcut.

3. Select the Catalogs table in the list of tables and queries. Move the Name , City , and State fields from the Available Fields list to the Selected Fields list.

4. Now select the Plants table in the list of tables and queries. You'll see that this does not clear your existing selections. Add the CommonName and LatinName fields to the Selected Fields list and click Next.

5. Because the report includes fields from two tables, the Report Wizard displays an additional panel, shown in Figure 9.11. This panel provides a shortcut way to group the report. Select the By Catalogs entry (it should be the default choice) and click Next.

   Figure 9.11. Grouping a report based on more than one table.

   

6. You don't want to add any grouping levels, so click Next again.

7. Choose to sort the records by CommonName and click Next.

8. Select the Outline 1 layout and click Next.

9. Select the Soft Gray style and click Next.

10. Name the new report CatalogsAndPlants and open it in preview mode. Figure 9.12 shows the completed report.

    Figure 9.12. A report of catalogs and plants.

    

Although the layout could still be improved, for the moment concentrate on the information displayed by this report. It shows, in boxes, information about each catalog: the Name, City, and State fields from the catalog. After that, it lists the common name and Latin name of each plant from that catalog. Note that the catalog information is not needlessly repeated for every single plant.

That's the effect of grouping in a report. Because you chose to group this report by catalog, the information about each catalog is presented only once. You can think of Access as setting up a number of buckets internally, one for each catalog. As it moves through the records for the report, it tosses each one in the appropriate bucket. Then each bucket is emptied onto the report in turn , showing the information that is shared by every item in the bucket followed by the information that is unique for each item.

A Peek at Report Design View

Similar to the other Access objects you've seen in this book, reports have more than one view. So far, you've been looking at reports in print preview view. The other view, design view, is analogous to the design view of tables, queries, and forms. It enables you to see the internal information that dictates the appearance of the report and the data it contains, and it enables you to make changes to this information.

Figure 9.13 shows a report open in design view. As you can see, quite a few windows and tools are available in this view.

Figure 9.13. A report open in design view.

Report design view offers four windows for making design changes:

• Toolbox Lets you add new controls to a report. Controls are the containers that display data on a report.

• Field List Lists all the data fields (from the underlying table or query) that are available to the report.

• Sorting and Grouping window Lets you control the way the report sorts and groups the data.

• Properties window Lets you change the appearance and behavior of controls on the report and the report itself.

As shown in Figure 9.13, toolbar buttons are available to show and hide each of these four windows. In addition, menu items appear on the View menu to show or hide each of these windows.

Although the Report Wizard creates useful reports, they're sometimes not quite perfect. A good example is the Plants report you created earlier in the chapter, which doesn't do a very good job of displaying pictures. Here's how you can use design view to fix the problem:

1. Select the Reports section in the Database window.

2. Select the Plants report.

3. Click the Design button on the Database Window toolbar.

4. After the report opens in design view, use the toolbar or menu items to hide the Toolbox, Sorting and Grouping window, and Field List (some of these might already be hidden). Make sure that the Properties window is visible; select View, Properties from the Access menus if it is not (or click the Properties button on the report design toolbar).

5. Click the drop-down arrow for the list at the top of the Properties window. This list shows all the controls on the report. Scroll through the list and select the Picture control. When you do this, the corresponding control on the report is selected, as indicated by the selection handles shown in Figure 9.14.

   Figure 9.14. Selecting a control in report design view.

   

6. With the Picture control selected, the Properties window shows the settings that control the way this control looks and behaves. For example, the Control Source property indicates that data displayed in this control comes from the Picture field in the underlying (or bound) table or query. Click in the Size Mode control, where it currently says Clip . Now select Zoom from the drop-down list that appears when you click in the control.

7. Click the Save button on the toolbar.

8. Select View, Print Preview ; the report displays with the pictures more nicely sized , as shown in Figure 9.15.

   Figure 9.15. The Plants report after modifying its design.

   

The reason the report looked bad in the first place is that the pictures in the database are of various sizes. Some are much larger than others. When the Report Wizard designed the original report, it made the control large enough to hold the largest picture. Then it set the Size Mode property of the control to Clip, which tells Access to display the pictures' actual sizes. So, although the largest of the pictures filled the control, the smaller pictures were left floating in a sea of white space.

By setting the Size Mode to Zoom, you told Access to expand or shrink the pictures to fit in the box, leaving as little empty space as possible without distorting the pictures. This way, the smaller pictures are increased in size to fill their allotted space on the page. The result is a much better looking report.

You might have noticed the third setting for the Size Mode property, Stretch. This setting tells Access to stretch the pictures to exactly fit their allotted space, distorting them if necessary.

You'll learn more about working with reports in design view in Chapter 14.