Introducing Access Reports

Introducing Access Reports

You often want printed listings of your data, and the Access reporting tools enable you to produce professional reports with ease. This hour introduces you to the report wizards and discusses the different reporting styles and options available. You learned how to produce printed listings in Hour 18, "Entering and Displaying Access 2003 Data." In this hour, you learn how to add flair to your reports. Keep in mind that discussions of Access's reporting wizards could fill an entire book. Although you will not be a complete master at the end of this hour , you'll have a better idea of what reporting tools you can use.

Unlike forms, a report often displays multiple records in a view that resembles the Datasheet view. The difference between the Datasheet view and a printed report is that the report provides summary statistics, fancy headings, footers, page numbers , and styles that accent your data. In addition, you can pick and choose exactly what data the report is to include as well as group that data to subtotal and total certain pieces of data in the database.

A report is not only a listing of multiple data values. Anytime you need to send Access data to paper, you must create a report. Therefore, a report might be a series of checks or mailing labels that you print.

Before you print any report, use the Print Preview to see the report on your screen. Often, you notice changes that you need to make, so previewing a report can save you time and paper. Your computer's print spooler (the area of memory that holds your report while printing) is too fast to stop quickly, so you usually end up printing the first few pages of a report even if you attempt to stop the printing.

When you are about to print a report, click the Reports object in the left pane of the Database window, select a report, and click the Preview button on the Database window's toolbar. Access can generate nice reports (see Figure 20.1).

Figure 20.1. The report preview shows how Access formats your report.

Rarely do you report all the data from a single table. Except for detailed reports, such as inventory listings and master customer listings, you almost always report part of a table or values from multiple tables.

Almost all reports that you generate, therefore, get their data from queries that you have created. If you need to report from part of a table's data or from multiple tables, create a query on which to base the report. Your queries can order the data the way you want to see it, and Access can then print your query's results.

Generating Simple Reports Using AutoReports

Access includes an AutoReport feature, which quickly generates reports from your Datasheet views. As Figure 20.2 shows, the AutoReport feature is just a listing of field names and their corresponding field values.

Figure 20.2. AutoReport generates simple reports.

Unlike the printed listings you get when you select File, Print from the Datasheet view, AutoReport formats your data in a readable manner without squeezing too much information on a single page.

To Do: Produce Quick Reports

If you need to run a quick report from a table, follow these steps to enable AutoReport to generate the report:

1. Display your data in a Datasheet view. If you want to report from a subset of data or from a collection of multiple tables, display the query's Datasheet view or apply a filter to the data.

2. Click the down arrow next to the New Object button on the toolbar.

3. Select AutoReport from the button's drop-down list. Access generates a report from the current Datasheet view and shows a preview of the report (shown in Figure 20.2).

4. If the preview shows what you want, click the Database toolbar's Print icon to print the report.

5. Click the Design button to close the AutoReport preview and to display the Report Design view (shown in Figure 20.3). The Report Design view can be difficult to understand when you first see it. (The Form Design view looks similar.) For now, don't worry about the specific screen elements. AutoReport created the report's design and saved you from having to use the Report Design view tools that you see in Figure 20.3.

   Figure 20.3. AutoReport enables you to avoid using the Report Design view.

   

6. Close the Report Design view and enter a report name if you want to save the AutoReport's design. If you think you will ever edit an AutoReport design later or generate the same report often, save the report. Generally, you use AutoReport only to generate quick reports. You will use the report wizards (described next) to generate more standard reports.

AutoReport generates reports using Access's report-designing tools. After you learn how to modify a Report Design view, you can use AutoReport to generate the foundation of a report and then change the report to make it look the way you want.

If you like using AutoReport for quick reports, check out the AutoForm feature. AutoForm creates fast and simple forms from your Datasheet views.

Generating Reports Using the Report Wizards

Access includes several report wizards that design reports according to the specifications you give. Select one of the report wizards by opening the Database window, clicking the Reports object (which displays the reports currently defined in the database), and clicking the New button to create a new report. The New Report screen (see Figure 20.4) appears.

Figure 20.4. Request a report from the New Report dialog box.

The New Report dialog box enables you to access the following reporting components :

• Design View A blank Report Design view on which you can add a new report's headers, footers, detail, and summary requests . When you want to generate a report from scratch, use the Design View in the New Report dialog box.

• Report Wizard Walks you through the report-generation process by enabling you to select the source tables and queries as well as the fields that you want in the final report. You will probably run Report Wizard more often than the other options because it generates less specific reports than other reporting wizards. The next section, "To Do: Use Report Wizard," explains how you can use Report Wizard to create reports.

• AutoReport: Columnar Generates a report that contains all the fields from the underlying table or query. The columnar report resembles the AutoReport, except that the columnar report makes better use of your report's page space and designs a more routine report. A columnar report can include titles and special fonts and can emphasize field data.

• AutoReport: Tabular Generates a report that displays on a single line the record of each source table or query by adjusting the font size to fit your report page. The tabular report looks much better than a simple Datasheet view and is often more useful than the generic AutoReport you learned about in the previous section. Figure 20.5 shows a sample of the wizard's tabular report. As you can see from the figure, Access is not always able to fit the complete field name at the top of a column of data. You can adjust the report's design to allow more room for the field name if you prefer.

  Figure 20.5. Tabular reports produce well-organized listings of your data.

  

• Chart Wizard Produces graphs from your data. Access graphs resemble Excel graphs, and you can control the format and style as well as the table from which Access graphs.

• Label Wizard A generic mailing-list report that produces mailing labels for all common labels. The mailing-label industry has a standard numbering system (the Avery numbering system , after the company that is perhaps best known for computerized mailing labels). Most office-supply stores sell mailing labels with an official Avery number, so you can format a report for your labels.

To Do: Use the Report Wizard

The second New Report screen option, Report Wizard, is probably the most common selection you make when you generate customized reports. The Report Wizard walks you through a series of steps to create a custom report from your tables and queries, and it includes many common reporting styles and special features that you need.

You must create a named query before generating a report based on that query. You cannot create a report based on a query that you have not yet written.

To start the Report Wizard, follow these steps:

1. Display the Database window.

2. Click the Reports object to display any database reports you've defined and to generate new ones.

3. Click the New button to display the New Report screen.

4. Select Report Wizard.

5. Open the New Report's drop-down list box to select from a list of tables and queries that reside in your database.

6. Click the OK button to display the Report Wizard's field selection screen (shown in Figure 20.6). The field selection screen displays a list of fields from your selected table or query that you can select for use as the report's data.

   Figure 20.6. Select the fields you want for your report.

   

7. Select the fields that you want in the final report by clicking the field in the Available Fields list and then clicking > to send the field to the Selected Fields list. You can also double-click a field name to send it to the Selected Fields list. As you can see when you open the Tables/Queries drop-down list box, you can select fields from several tables and queries in addition to the primary source you selected in step 5.

8. After you select the fields for the report, click Next.

Grouping Report Summaries

Now that you have selected the report fields, you must indicate how the report is to be summarized. Rarely will you want to print a data listing without requesting a report summary that includes totals and subtotals. Depending on the type of numeric fields you have selected for the report, the Report Wizard prompts you for subtotal and total summary information or grouping information. If your report contains numeric data, the Report Wizard lets you choose the grouping options (shown in Figure 20.7). Use this dialog box to indicate how you want Access to group your report subtotals.

Figure 20.7. Indicate how Access is to produce the subtotals and totals.

Access totals and subtotals reports based on a group's numeric fields, not text fields. The numeric fields enable the Report Wizard to produce sub totals and total summaries, whereas no such summaries are possible with text fields.

If Access does not display the Summary dialog box, the Report Wizard prompts you for grouping information, and you can select the field on which you want your data grouped. When you request grouping, you are telling Access how to sort your report. If you grouped by a City field, for example, the report would sort the report in City order. The subtotals are then summed for each city in the report data.

You might have to run the Report Wizard a couple of times to group the report the way you prefer. Be sure to select only those numeric fields that produce proper subtotals. If you report a division number field and that field is numeric, for example, you wouldn't want Access to subtotal and total the division number. If you print a report with customer past-due balances, however, you do want to print a subtotal for each customer and an overall grand total of past-due balances .

The grouping field should be nonnumeric groups, such as customer IDs, for which you are printing a detailed list. Although the same customer might appear on several report lines, you want Access to subtotal only when the customer ID changes to the next customer in the report. The grouping doesn't total or subtotal the customer IDs, but the totals and subtotals that appear all break (stop) when the customer ID changes. The grouping capability keeps multiple customer IDs from appearing down the column when you print multiple records for the same customer.

As you click each field that you selected, the grouping dialog box displays a preview of your report, showing how the grouping will fall out. Generally, the grouping options and preview give you enough information to decide on a grouping scheme.

If you want to group on multiple fields, the group order you select determines the priority that Access uses to group the data. The highest priority grouping level (if you select multiple groups) appears on the left of the report. You can change the highest priority level by clicking the Priority button on another grouping level. The second grouping level appears to the right of the first one, and so on. Generally, multifield grouping gets confusing. If you want to add such grouping levels, click every group level from the series of fields that Access displays. The Report Wizard prioritizes the multifield groups in the order that you select the fields.

You can group by a maximum of four field-grouping levels. If you need to group by more than four fields, you have to edit the report design or create a report from scratch. Rarely do you need more than four groups.

If the Report Wizard has presented you with the grouping view screen rather than the subtotal and total summary screens described here, you need only click the field by which you want to group the report. If you are producing a customer balance report, for example, the Report Wizard might enable you to group by either the Customer ID field or the Customer Order Number field.

After you set up the grouping levels that you need, click Next to select a sort and summary order for the report.

The Report's Sort and Summary Order

When you see the sorting and summary screen (see Figure 20.8), you are almost done creating your report. Select from one to four fields that you want Access to use for sorting your data. If you are reporting from a query that already contains sorted data, and if you generate the report so that the data groups in the order of those sorted fields, don't select any fields by which to sort. If you want to sort by one or more fields that do not enter the reporting system already sorted, however, select up to four fields and click the button labeled Ascending to sort in ascending order.

Figure 20.8. Select from one to four fields by which to sort.

The Ascending button changes to Descending when you click it, so you can then select a descending sort.

If you requested totals and subtotals in earlier Report Wizard steps, you will see a Summary Options button. These options display the Report Wizard's Summary Options screen. Each of your report's numeric fields appears in the box, so you can select any or all these summaries for that field:

• Sum Requests a total for the field

• Avg Requests an average for the field

• Min Requests that Access highlight the minimum value in the field

• Max Requests that Access highlight the maximum value in the field

You cannot display summaries if your report has no numeric fields or if you are grouping by the numeric fields.

Choosing a Report Layout and Style

Click OK to close the Summary Options screen and then click Next to choose a report layout. Click through the various layouts to select one that fits your needs. As you click layouts, Access displays thumbnail sketches of those layouts. If you click the Block style, for example, you see that Access displays boxes around your report fields.

After you select the layout, click Next to select from the Report Wizard's style screen (shown in Figure 20.9). From this dialog box, you can add the finishing touches to your report.

Figure 20.9. The Report Wizard's style screen determines the overall appearance of your report.

Click Next, enter a report title if you don't want to use the original source table or query name, and click Finish to generate the completed report. Sometimes Access takes several minutes to generate the report, especially if the source tables and queries contain many records and your report contains several grouping levels. When finished, Access prints a preview of your report so that you can look at your handiwork.