The look and feel of the reports you design will vary depending on the type of information you want to present. If you’re creating a simple report that lists book sales in the southwestern region of the United States, you can use the Report Wizard to create a basic two-column report that lists only regions and sales in a matter of minutes. If you’re creating a more complex report that prints a student roster for your graduate program, complete with pertinent transcript subreports within each report, your planning might take several hours as well as the input of a number of people to ensure that the report meets the needs of the users who will read it.
Whether you want to print a report that gives a bare-bones listing of data in a table, a complex grouped report that summarizes data, or a report that displays linked data in subreports, plan to spend some time thinking through what data your report needs to display and how the data should be presented. Planning and foresight will save you editing and revision time later on. As you think about what you want to accomplish with the report you’re creating, consider these questions:
Access has a Report Wizard to aid you in creating reports. The various report types created by the Wizard can save time, and one of the Wizard’s selections may be exactly the type of report you need. Sometimes, however, you need a report type the Wizard doesn’t create. When that’s the case, you can create a report starting with a blank report in Design view, adding controls to the report grid manually, and setting report properties as desired. In the following sections, I’ll review several of the questions you should consider when designing reports.
Although you can save a form as a report, this "shortcut" usually ends up taking longer than creating a report from scratch because so much work has to be done to remove elements that work only on forms and to add report-specific components used to group and summarize data. I don’t recommend using this method to create a report.
Reports can use the same types of record sources that forms use: tables, queries, SQL statements, and recordsets. You select (or change) a record source for a report the same way you would for a form. For example, if you were designing a simple name and address list report, a table of contact data could be the report’s record source. A report that needs to list only a subset of data in a table could be based on a query that selects the output you need; for example, selecting records for the Southeast sales region and the year 2000.
A report that displays only summarized data could be based on a Totals query that uses the Sum or Count function to present the total dollar amount of sales or the count of orders. Just as for form record sources, SQL statements and recordsets can be used instead of queries as report record sources.
For more details on creating queries, see Chapter 9, "Using Queries to Select Data"; for information on using various types of record sources, see Chapter 5, "Creating Forms for Entering, Editing, and Viewing Data."
Reports have several features that don’t correspond to any form feature: One of them is grouping. A grouped report divides data into bands that correspond to the values in fields that you select for each group level. An example is grouping a name and address report by State and then by LastName. You can have up to ten group levels in a report, but reports rarely need more than three or four levels, and often one group level is all you’ll need. The Report Wizard has a Grouped Report selection that you can use to create a grouped report, but before you start the wizard you should create a query to use as the grouped report’s record source. This query should contain all the tables you need to obtain the data you want to include in the report, with just the fields needed for the report selected as query columns.
The fields used to create report groups are also fields used for sorting. Even if you don’t want to separate your data into distinct groups—and you don’t need group headers or footers—you can still use the Sorting and Grouping dialog box to sort the report dataas you want. This is especially useful if the report is based directly on a table. You can base several reports on the same table, sorting each by different fields, to produce very different reports from the same data.
For example, to create a report in the Crafts database that lists books and videos, grouped by author, you need a query that contains tblBooksAndVideos, tblBookAuthors, and tblAuthors, as shown in Figure 7-3. These three tables define the many-to-many relationship between books and authors.
Figure 7-3. You can use this query with three linked tables as a grouped report’s record source.
You can also create three-table queries for the Books–Sources and Books–Specialties many-to-many relationships. For an all-in-one report, you can create a query containing all the tables needed for books and videos, authors, sources, and specialties.
For more information about creating queries to select data, see Chapter 9, "Using Queries to Select Data."
Grouped reports require a record source (usually a query) that contains all the information needed to set up the groups. However, you have another way to create a grouped report: by using subreports to display linked data. Just as you can with linked forms, you can select the "one" side of a one-to-many relationship as a report’s record source and select the "many" side of the relationship as the record source of another report, and then you can embed the report bound to the "many" table as a subreport on the main report. Because the record source tables are linked on the key field, the subreport will always display the correct records linked to the main report record.
No wizard exists for creating reports with embedded subreports. You have to create such a report yourself in Design view, either by dragging the report you want as the subreport to the main report or by placing a Subform/Subreport control on the report and selecting the report intended to be the subreport as its source object.
For a discussion of various types of reports with embedded subforms and subreports, see "Adding Embedded Subreports and Subforms to Reports."
The Report Wizard has several selections for creating reports of different types. To start the Report Wizard, double-click Create Report By Using Wizard on the Reports tab of the Database window. This wizard creates grouped reports in a variety of formats.
See "Grouped Reports," for more details on working with the Report Wizard.
There are several other ways to create a new report: If you click New at the top of the Database window, the New Report dialog box appears, offering several choices, as shown in Figure 7-4.
Figure 7-4. The New Report dialog box has selections for creating various types of reports.
In addition, the New Object selector on the Database toolbar offers an AutoReport selection, which creates a not particularly attractive plain columnar report. Table 7-1 lists the available choices for report layouts and indicates where the selection is available. The sections that follow describe how to create reports using these layouts.
Table 7-1. Available report layouts
|Layout selection||Available from||Description|
New Report, Chart Wizard
Data from selected fields is charted in a variety of chart formats, using MS Graph.
New Report, AutoReport: Columnar
Fields are laid out top to bottom, with visible borders around each text box control.
New Report, Design View
Generates a report based on a previously prepared crosstab query.
New Report, Design View
Lets you manually create a report with a datasheet subform.
New Report, Design View
One or more embedded subreports are manually placed on the report; the subreports can be linked or unlinked.
New Report, Design View
No controls are automatically placed on the report; you can place controls where you want them.
Report Wizard, choice of six layouts
Groups data by selected fields, with a choice of indented or left-aligned formats.
New Report, Label Wizard
Data is formatted to be printed on one of a number of commercial labels or according to a custom label format.
New Report, Design View
Report is manually set up as a multicolumn report in the Page Setup dialog box.
New Report, Design View
Creates a report with an embedded PivotChart subform, formatted to remove interactive elements.
New Report, Design View
Creates a report with an embedded PivotTable subform.
New Report, AutoReport: Tabular
Fields are laid out left to right, with column headings.
To create a simple columnar report in the minimum amount of time, select a table or query in the Database window, and then select AutoReport from the New Object selector on the Database toolbar, as shown in Figure 7-5.
Figure 7-5. The New Object selector lets you create an AutoReport from the Database toolbar.
The report, shown in Figure 7-6, is created automatically. It displays all the fields in the selected table or query, arranged in a single column from top to bottom. You can also create a columnar report from the New Report dialog box, as described in the "Columnar Reports" section. You’ll get exactly the same report whether you use the New Object: AutoReport button or the New Report dialog box selection.
Figure 7-6. Selecting AutoReport creates this report.
When you use the Report Wizard to create a grouped report, you have six choices for the report layout. The differences in layout are more a matter of taste than functionality, and all the other wizard selections are the same, so you can identify the layout you prefer by looking at the layout illustration on the wizard screen. Whichever layout you select, you’ll probably need to modify it somewhat to create an attractive report.
To create a grouped report, first prepare a query (such as qryBooksVideosAll) containing all the data needed for the report groups, as described in "Using Grouped Reports." Then follow these steps:
Figure 7-7. The Report Wizard offers a selection of fields for your report.
Figure 7-8. If the report’s record source has more than one table, use this page of the wizard to select the primary table for the report.
Figure 7-9. Select fields for group levels.
Figure 7-10. Select sort levels.
Figure 7-11. In this example, the Stepped layout and landscape orientation have been chosen for the report.
Figure 7-12. Select a report style.
Figure 7-13. Give the report a title on the last page of the wizard.
Figure 7-14 shows the finished report in print preview.
This report is a good start but needs some fine-tuning. None of the standard layouts is ideal, at least to my taste. The Stepped layout takes up an entire column for each group, whereas the Align Left layouts tend to use too many lines and boxes. For a clean, space-saving layout, try selecting the Stepped layout and then moving the group field controls and the first detail control to the left side of the report.
Figure 7-14. The finished report looks like this in print preview.
Troubleshooting - My report breaks in the middle of a group of data
When a grouped report is printed, data that ought to be on a single page is sometimes broken up by a page break so that part of it prints on one page and the rest on the next page.
To prevent a group from being broken over two pages, open the Sorting And Grouping dialog box (in Design view), and specify Whole Group for the KeepTogether property for the group you want to keep together. For example, on rptBooksAndVideos, I wanted all the information on one book to be kept together, so I set KeepTogether to Whole Group for the Title group. Now if the information for a specific book title won’t fit at the bottom of a page, it will be moved to the beginning of the next page, instead of breaking in the middle of the group.
Troubleshooting - I want to suppress repetitive data in grouped reports
If the same data is repeating over and over in a grouped report, how can you make it print only once per group? For example, in a grouped report with a staggered layout, you generally don’t want to have the same data (such as a country name) repeating for every item in the Country group. Fortunately, text boxes on Access reports have a special property that can be used to suppress the printing of repetitive information.
For any text box that might contain repetitive data, set the HideDuplicates property to Yes. This allows the first instance of a particular value in the bound field to be printed but suppresses the printing of the same value on following lines in the same section.
Figure 7-15 shows a simple stepped report based on data in the Crafts database, with the HideDuplicates property set to Yes for the txtPublicationYear and Publisher text boxes. (See Figure 7-59 for a "before" version of this report, in which the HideDuplicates property was left at the default setting No and publisher data repeats.)
Figure 7-15. A stepped report uses the HideDuplicates property to show only the first instance of each value in a group.
You can use a wizard-generated report as is, and a wizard-generated report may be quite adequate for a quick data printout, say for proofreading purposes, as mentioned earlier. But for reports designed to be saved and studied, I recommend making the following changes to give your reports a polished appearance. You won’t need to make all of these changes for every report, but you’ll usually need to make most of them.
=IIf([Cost]>0,[CurrencyType] & Format([Cost],"#,###.00"))
=IIf([PublisherCode],"Publisher: " & [PublisherCode], "No publisher entered")
Figure 7-16. Set the KeepTogether property to Whole Group to prevent an author group from splitting.
The third page of the sample report, modified in these ways, is shown in Figure 7-17.
Figure 7-17. This report is more attractive after fine-tuning.
Changing Report Names, Titles, and Captions
Access reports have names (listed in the Database window) and captions (displayed as text in the report’s title bar). Additionally, reports—at least those you create with the Report Wizard—have a Title label control in the report header. You can examine (and change) a report’s caption by using the Caption property in the report’s properties sheet, as shown in Figure 7-18. You can’t change a report’s name in its properties sheet because no Name property is displayed there.
Figure 7-18. You can change a report’s caption by changing the Caption property.
Reports do have a Name property, however. You can print the report name using this property by placing an expression such as the following in the Control Source property of a text box:
="Report name: " & [Name]
A text box listing the report name (say, in the report’s page footer) is a handy way of identifying the name of a printed report, in case you need to revise it.
The Report Wizard uses the term "title" on the last page of the wizard. This can be confusing since reports don’t have a Title property. The text you enter as the report’s title will be used as both caption and name. This text will also be displayed in the title label control in the report header. This situation presents a problem for users who want to name their database objects in accordance with a particular naming convention, such as the Leszynski Naming Convention (LNC) discussed in Chapter 20, "Customizing Your Database Using VBA Code." If you use the LNC naming convention for database objects, your report names should start with the rpt tag. If you title the report rptBooksByAuthorAndPublisher in the Report Wizard, both the report’s title bar (in print preview) and the large title label in the report header will display rptBooksByAuthorAndPublisher, which looks strange.
If you want the report’s caption to differ from the report’s name (which is generally a good idea), you have to give the report a title in the wizard and then rename it in the Database window, as shown in Figure 7-19. To rename a report in the Database window, select the report name and press F2 to switch to edit mode. Edit the name as desired, and press Enter.
Figure 7-19. You can rename a report in the Database window.
Alternatively, you can give the report the title rptBooksByAuthorAndPublisher in the wizard and then change the caption to Books by Author and Publisher in the report’s properties sheet and in the Title label.
Columnar reports, though plain in appearance, are useful for proofreading and checking data; their one-field-per-row layout leaves lots of room for marking corrections. The quickest way to create a columnar report is to click the New Object: AutoReport button on the toolbar, as described in the "Creating a Columnar Report Quickly" section. However, you can also create a columnar report by following these steps:
Figure 7-20. This report was created using the AutoReport: Columnar selection.
You can improve this report by adjusting control sizes as needed or by using expressions to concatenate address data, as described in the Troubleshooting sidebar "My Report Has Gaps in Its Data".
Tabular reports are most useful when you want a report laid out like a worksheet, usually to display financial data.
To create a tabular report, follow these steps:
This report type (see Figure 7-21) looks much like the stepped report except that it lacks the group levels.
Figure 7-21. The finished tabular report looks like this.
Although a tabular report somewhat resembles an Excel worksheet, it’s significantly more difficult to work with than a worksheet or a datasheet form. This report doesn’t have adjustable columns like a worksheet, just text boxes and labels that you must move manually if you want to resize the columns.As is typical of reports created using the Report Wizard, you’ll need to resize some labels and text boxes to display all of their captions or data without truncation.
Chart reports are used when you want to display your data graphically. The MS Graph component used to create Chart reports is showing its age, but it is still useful. However, you may prefer to just create a PivotChart instead. (See the "PivotChart Subreports" section) for details on this method.)
To create a meaningful chart, you need to select appropriate fields; generally charts are based on queries that preselect data, perhaps from several linked tables. For a pie chart, you need only two fields: one for the series, and one for the data. For example, you have undoubtedly seen a newspaper pie chart that shows government expenditures by department—the dollar amount of expenditures is the data on the pie chart, while the department names are the series.
Other chart types require three fields: one for the series (the horizontal labels), one for the axis (the vertical labels), and one for the data that is summarized. To extend the government expenditures example, a bar graph could have expenditures as data, department names as the series field at the bottom of the chart, and years as the axis field along the left side of the chart.
When creating a report with a chart, you usually won’t find all the data you need in a single table, so start by preparing a query that contains the data you want to display in the chart.
For information about creating a query, see Chapter 9, "Using Queries to Select Data."
After you’ve created the query, follow these steps:
Figure 7-22. Select fields for the chart in the Chart Wizard.
Figure 7-23. Select a chart type for the Chart report.
Figure 7-24. The Chart Wizard allows you to preview the selected chart.
Click Preview Chart to preview the chart in a small window. Click Close to close the Sample Preview window. Then click Next.
Figure 7-25. The name you give the chart report is not used as a report name or caption.
Figure 7-26. The finished Chart report looks like this in Design view.
When you look at a chart report in Design view, you won’t see the real data, only dummy data. This is a peculiarity of MS Graph charts. The newer PivotCharts don’t have this constraint, so if you find it easier to work with a chart where you can see the real data, PivotCharts are a better choice than Chart reports. See the "PivotChart Subreports" section and Chapter 12, "Using PivotTables and PivotCharts to Analyze Data," for more information on creating PivotCharts.
Figure 7-27. The finished chart report has been resized and named according to the LNC.
You might need several runs through the Chart Wizard to get a usable chart. Unlike PivotCharts, MS Graph charts can’t be easily adjusted after creation. For this reason, I recommend that you use PivotCharts on reports when you need to display data graphically. (See "PivotChart Subreports.")
Use the Label Wizard to create mailing labels and other types of labels. The following example creates Avery #5161 mailing labels for the tblAuthors table in the sample Crafts database.
To create a label report, follow these steps:
Figure 7-28. Select a label type in the Label Wizard.
Figure 7-29. Select font characteristics for the labels.
Figure 7-30. Place name and address fields on the label.
Figure 7-31. Select a field for sorting the labels.
Figure 7-32. Name the Labels report on the last page of the wizard.
In the previous sections, you learned how to use the Report Wizard and the New Report dialog box to generate reports using Access’s pre-designed report styles. But not all the reports you create will fit into one of the automated report styles you’ve seen thus far: Some reports you’ll need to create manually. The following sections explain reports you may want to design on your own with Access, including free-form reports, multicolumn reports, and crosstab reports.
A free-form report is one that you put together any way you like. No controls are placed on the report by default; you place controls on the report manually. To create a free-form report, follow these steps. (The same steps are used to start a multicolumn or crosstab report.)
Figure 7-33. Start a free-form report when none of the standard report formats meet your needs.
A blank report opens in Design view. Use the Toolbox to place controls on the report in the layout you want, format them as desired, and then open the Sorting And Grouping dialog box to set up report groups and sort the data as you prefer. See Chapter 6, "Working with Form Controls," for more information on working with controls; controls work much the same on forms and reports.
Multicolumn reports are useful for displaying data from one or a few fields in a space-saving manner. They are typically used to produce phone directories, lists of product codes, and other such information laid out in several columns. The Crafts Categories report in the sample Crafts database is an example of a multicolumn report.
Access has no wizard for creating a multicolumn report, although one is certainly needed since the settings used to create a multicolumn report in Access are so well hidden that many users don’t even realize that it’s possible to create such a report.
To create a multicolumn report, follow these steps:
Figure 7-34. Set the column properties for a multicolumn report.
In Design view, a multicolumn report doesn’t look different from a standard (single column) report—that is, there’s no indication of the column boundaries on the report grid. This means that when you place controls on the report grid, you need to keep them within the boundaries of a single column width. The report header and footer, however, will span the entire report width.
Figure 7-35 shows the finished two-column report.
Figure 7-35. The finished two-column report looks like this.
If your report is both grouped and multicolumn, you can choose to start each group in a new column by setting the NewRowOrCol property of the group header to Before Section. Figure 7-36 demonstrates this option for a report based on qryBooksVideosSpecs.
Figure 7-36. You can set a group header’s properties to start a new group in a new column.
Figure 7-37 shows the grouped three-column report in print preview.
Figure 7-37. A three-column grouped report looks like this in print preview.
Numbering Report Items
A report that lists many items often needs to have the items numbered sequentially so that they can be conveniently referenced. This is especially useful when the items don’t have IDs that can be used to identify them. The RunningSum property (available only for controls on reports) can be used to dynamically number report detail items, as follows:
=[txtRunningSum] & "."
Figure 7-38 shows the Books By Year report with consecutive numbers for books in each year group, which are handy for referencing the books.
Figure 7-38. Use the RunningSum property to create a report with numbered detail items.
If you’ve created a crosstab query that summarizes data in a useful manner, and you want to print and share the summarized data with other department members, you can generate a crosstab report to print the output of the crosstab query.
To create a crosstab report, follow these steps:
If you want, you can place a line control under the labels used as column headings.
Figure 7-39 shows the crosstab report in print preview.
Figure 7-39. The crosstab report in print preview looks like this.
tip - Save time revising your report structure