Chapter 37
Presenting Data with Access Reports
IN THIS CHAPTER
Looking at the different types of Access reports
Creating reports with a Report Wizard
Printing and saving the report
It’s hard to underestimate the importance of reports in database applications. Many people who never work with an Access application in person use reports created by Access. A lot of maintenance work on database projects involves creating new and enhancing existing reports. Access is well known and respected for its powerful reporting features.
Reports provide the most flexible way of viewing and printing summarized information. They display information with the desired level of detail, while enabling you to view or print your information in many different formats. You can add multilevel totals, statistical comparisons, and pictures and graphics to a report.
In this chapter, you learn to use the Report Wizard as a starting point. You also learn how to create reports and what types of reports you can create with Access.
Introducing Reports
Reports present a customized view of your data. Report output is viewed onscreen or printed to provide a hard copy of the data. Very often, reports provide summaries of the information contained in the database. Data can be grouped and sorted in any order and can be used to create totals that perform statistical operations on data. Reports can include pictures and other graphics as well as memo fields in a report. If you can think of a report you want, Access probably supports it.
Identifying the different types of reports
Three basic types of reports are used by most businesses:
- Tabular reports: Print data in rows and columns with groupings and totals. Variations include summary and group/total reports.
- Columnar reports: Print data and can include totals and graphs.
- Mailing label reports: Create multicolumn labels or snaked-column reports.
Tabular reports
Tabular reports are similar to a table displaying data in rows and columns. Figure 37.1 is a typical tabular report (rptProductsSummary) displayed in Print Preview.
FIGURE 37.1 An example tabular report displayed in Print Preview
Unlike forms or datasheets, tabular reports often group data by one or more fields. Often, tabular reports calculate and display subtotals or statistical information for numeric fields in each group. Some reports include page totals and grand totals. You can even have multiple snaked columns so that you can create directories (such as telephone books). These types of reports often use page numbers, report dates, or lines and boxes to separate information. Reports may have color and shading and display pictures, business graphs, and memo fields. A special type of summary tabular report can have all the features of a detailed tabular report but omit record details.
Columnar reports
Columnar reports generally display one or more records per page, but they do so vertically. Columnar reports display data very much as a data entry form does, but they’re used strictly for viewing data and not for entering it. Figure 37.2 shows part of a columnar report (rptProducts) in Print Preview.
FIGURE 37.2 A columnar report showing report controls distributed throughout the entire page
Another type of columnar report displays one main record per page (like a business form) but can show many records within embedded subreports. An invoice is a typical example. This type of report can have sections that display only one record and at the same time have sections that display multiple records from the “many” side of a one-to-many relationship — and even include totals.
Figure 37.3 shows an invoice report (rptInvoice) in Report view.
FIGURE 37.3 An invoice report
In Figure 37.3, the information in the top portion of the report is on the “main” part of the report, whereas the product details near the bottom of the figure are contained in a subreport embedded within the main report.
Mailing label reports
Mailing labels (shown in Figure 37.4) are also a type of report. Access includes a Label Wizard to help you create this type of report. The Label Wizard enables you to select from a long list of label styles. Access accurately creates a report design based on the label style you select. You can then open the report in Design view and customize it as needed.
FIGURE 37.4 A typical mailing label report
Distinguishing between reports and forms
The main difference between reports and forms is the intended output. Whereas forms are primarily for data entry and interaction with the users, reports are for viewing data (either onscreen or in hard-copy form). Calculated fields can be used with forms to display an amount based on other fields in the record. With reports, you typically perform calculations on groups of records, a page of records, or all the records included in the report. Anything you can do with a form — except input data — can be duplicated by a report. In fact, you can save a form as a report and then refine it in the report Design view.
Creating a Report, from Beginning to End
The report process begins with your desire to view data, but in a way that differs from a form or datasheet display. The purpose of the report is to transform raw data into a meaningful set of information. The process of creating a report involves several steps:
Defining the report layout
You should begin by having a general idea of the layout of your report. You can define the layout in your mind, on paper, or interactively using the Report Wizard. When laying out a report, consider how the data should be sorted (for example, chronologically or by name), how the data should be grouped (for example, by invoice number or by week), and how the size of the paper used to print the report will constrain the data.
Assembling the data
After you have a general idea of the report layout, assemble the data needed for the report. Access reports use data from two primary sources:
- A single database table
- A recordset produced by a query
You can join many tables in a query and use the query’s recordset as the record source for your report. A query’s recordset appears to an Access report as if it were a single table.
As you learned in Chapter 36, you use queries to specify the fields, records, and sort order of the records stored in tables. Access treats a recordset data as if it were a single table (for processing purposes) in datasheets, forms, and reports. When the report is run, Access matches data from the recordset or table against the fields specified in the report and uses the data available at that moment to produce the report.
Creating a report with the Report Wizard
Access enables you to create virtually any type of report. Some reports, however, are easier to create than others, especially when a Report Wizard is used as a starting point. Like form wizards, the Report Wizard gives you a basic layout for your report, which you can then customize.
The Report Wizard simplifies laying out controls by stepping you through a series of questions about the report that you want to create. In this section, you use the Report Wizard to create tabular and columnar reports.
Creating a new report
The Ribbon contains several commands for creating new reports for your applications. The Create tab of the Ribbon includes the Reports group, which contains several options such as Report, Labels, and Report Wizard. To work through the report creation process in predefined steps, first select the table or query that has the data for the report in the Navigation pane, and then click the Report Wizard button. The first Report Wizard dialog box (shown in Figure 37.5) appears.
FIGURE 37.5 The Report Wizard dialog box after selecting a data source and fields
In Figure 37.5, tblProducts has been selected as the data source for the new report. Under the Tables/Queries drop-down list is a list of available fields. When you click a field in this list and click the right-pointing arrow, the field moves from the Available Fields list to the report’s Selected Fields list. The example in the figure shows the ProductID, Category, Description, QtyInStock, RetailPrice, and Cost fields added to the Selected Fields list.
You’re limited to selecting fields from the original record source you started with. You can select fields from other tables or queries by using the Tables/Queries drop-down list in the Report Wizard. As long as you’ve specified valid relationships so that Access properly links the data, these fields are added to your original selection and you use them on the report. If you choose fields from unrelated tables, a dialog box asks you to edit the relationship and join the tables. Or you can return to the Report Wizard and remove the fields.
After you’ve selected your data, click Next to go to the next wizard dialog box.
Selecting the grouping levels
The next Report Wizard dialog box enables you to choose which field(s) to use for grouping data. Figure 37.6 shows the Category field selected as the data grouping field for the report. The field selected for grouping determines how data appears on the report, and the grouping fields appear as group headers and footers in the report.
FIGURE 37.6 Specifying the report’s grouping
Groups are most often used to combine data that are logically related. One example is grouping all products by product category. Another example is choosing to group on CustomerID so that each customer’s sales history appears as a group on the report. You use the report’s group headers and footers to display the customer name and any other information specific to each customer.
The Report Wizard lets you specify as many as four group fields for your report. You use the Priority buttons to change the grouping order on the report. The order you select for the group fields is the order of the grouping hierarchy.
Select the Category field as the grouping field and click the > button to specify a grouping based on category values. Notice that the picture changes to show Category as a grouping field, as shown in Figure 37.6. Each of the other fields (ProductID, Description, QtyInStock, RetailPrice, and SalesPrice in the example) selected for the report will appear in the Category group’s details section.
Defining the group data
After you select the group field(s), click the Grouping Options button at the bottom of the dialog box to display the Grouping Options dialog box, which enables you to further define how you want groups displayed on the report.
For example, you can choose to group by only the first character of the grouping field. This means that all records with the same first character in the grouping field are grouped. If you group a customers table on CustomerName, and then specify grouping by the first character of the CustomerName field, a group header and footer appears for all customers whose name begins with the same character. This specification groups all customer names beginning with the letter A, another group for all records with customer name beginning with B, and so on.
The Grouping Options dialog box enables you to further define the grouping. This selection can vary in importance, depending on the data type.
The Grouping Intervals list box displays different values for various data types:
- Text: Normal, 1st Letter, 2 Initial Letters, 3 Initial Letters, 4 Initial Letters, 5 Initial Letters
- Numeric: Normal, 10s, 50s, 100s, 500s, 1000s, 5000s, 10000s, 50000s, 100000s
- Date: Normal, Year, Quarter, Month, Week, Day, Hour, Minute
Normal means that the grouping is on the entire field. In this example, use the entire Category field.
Notice that the grouping options simplify creating reports grouped by calendar months, quarters, years, and so on. This means that you can easily produce reports showing sales, payroll, or other financial information needed for business reporting.
If you displayed the Grouping Options dialog box, click the OK button to return to the Grouping Levels dialog box, and then click the Next button to move to the Sort Order dialog box.
Selecting the sort order
By default, Access automatically sorts grouped records in an order meaningful to the grouping field(s). For example, after you’ve chosen to group by Category, Access arranges the groups in alphabetical order by Category. However, you can’t be sure of the order of the records within the group, so it’s a good idea to specify a sort within each group. As an example, your users might want to see the product records sorted by Retail Price in descending order so that the most expensive products appear near the top for each category group.
In this example, Access sorts data by the Category field. As Figure 37.7 shows, the data is also sorted by Description within each group.
FIGURE 37.7 Selecting the field sorting order
Sort fields are selected by the same method you use for selecting grouping fields. You can select sorting fields that haven’t been chosen for grouping. The fields chosen in this dialog box affect only the sorting order in the data displayed in the report’s Detail section. Select ascending or descending sort by clicking the button to the right of each sort field.
Selecting summary options
Near the bottom of the sorting screen of the Report Wizard is a Summary Options button. Clicking this button displays the Summary Options dialog box (shown in Figure 37.8), which provides additional display options for numeric fields. All the numeric and currency fields selected for the report are displayed and may be summed. Additionally, you can display averages, minimums, and maximums.
FIGURE 37.8 Selecting the summary options
You can also decide whether to show or hide the data in the report’s Detail section. If you select Detail and Summary, the report shows the detail data, whereas selecting Summary Only hides the Detail section and shows only totals in the report.
Finally, checking the Calculate percent of total for sums check box adds the percentage of the entire report that the total represents below the total in the group footer. If, for example, you have three products and their totals are 15, 25, and 10, respectively, 30%, 50%, and 20% shows below their total (that is, 50) — indicating the percentage of the total sum (100%) represented by their sum.
Clicking the OK button in this dialog box returns you to the sorting screen of the Report Wizard. There you can click the Next button to move to the next wizard screen.
Selecting the layout
The next step in the Report Wizard affects the look of your report. The Layout area enables you to determine the basic layout of the data. The Layout area provides three layout choices that tell Access whether to repeat the column headers, indent each grouping, and add lines or boxes between the detail lines. As you select each option, the picture on the left changes to show how the choice affects the report’s appearance.
You choose between Portrait (up-and-down) and Landscape (across-the-page) layout for the report in the Orientation area. Finally, the Adjust the field width so all fields fit on a page check box enables you to cram a lot of data into a little area. (A magnifying glass may be necessary!)
For this example, choose Stepped and Portrait, as shown in Figure 37.9. Then click the Next button to move to the next dialog box.
FIGURE 37.9 Selecting the page layout
Opening the report design
The final Report Wizard dialog box contains an area for you to enter a title for the report. This title appears only once, at the very beginning of the report, not at the top of each page. The report title also serves as the new report’s name. The default title is the name of the table or query you initially specified as the report’s data source.
Next, choose one of the option buttons at the bottom of the dialog box:
- Preview the report
- Modify the report’s design
For this example, leave the default selection intact to preview the report. Click Finish and the report displays in Print Preview (see Figure 37.10).
FIGURE 37.10 An example report displayed in Print Preview
Adjusting the report’s layout
There are a few small issues with the report you see in Figure 37.10. The Access Report Wizard has chosen the fonts and overall color scheme, which may not be what you had in mind. Also, the Retail Price column isn’t quite wide enough to show the column heading.
The Report Wizard displays the new report in Print Preview. Right-click the report’s title bar and select Layout View from the shortcut menu. Figure 37.11 shows a report in Layout view.
FIGURE 37.11 Layout view is useful for resizing controls in a columnar report.
In Figure 37.11, the Category column has been shrunk to eliminate some wasted space, the Description column has been widened to the left to fill that space, and the remaining columns have been separated so that the column headings show and aren’t all pushed together. Working with controls in Layout view for a report is identical to working with them in Layout view for a form. To shrink a column’s width, for example, click one of its controls and drag the right edge of the control to the left.
Choosing a theme
After you adjust the layout, you can use controls in the Themes group of the Report Design Tools ⇒ Design tab to change the report’s colors, fonts, and overall appearance. The Themes button opens a gallery containing several dozen themes (see Figure 37.12).
FIGURE 37.12 Choosing a theme for the report
Themes are an important concept in Access 2013. A theme sets the color scheme, selected font face, font colors, and font sizes for Access 2013 forms and reports. As you hover the mouse over the theme icons in the gallery, the report open in Layout view behind the gallery instantly changes to provide a Live Preview of how the report would look with the selected theme.
Each theme has a name, like Office, Facet, Organic, and Slice. Theme names are useful when you want to refer to a particular theme in the application’s documentation or in an e-mail or other correspondence. Themes are stored in a file with a THMX extension, in the Program FilesMicrosoft OfficeDocument Themes 15 folder. Themes apply to all the Office 2013 documents (Word, Excel, and Access), making it easy to determine a style to apply to all of a company’s Office output.
As the shortcut menu in Figure 37.12 indicates, you can apply the selected theme just to the current report (Apply Theme to this Object Only), all reports (Apply Theme to All Matching Objects), or all forms and reports in the application (Make This Theme the Database Default). There’s even an option to add the theme as a button to the Quick Access Toolbar, an extremely useful option for selectively applying the theme to other objects in the database.
Creating new theme color schemes
Access 2013 provides several default themes, with each theme consisting of a set of complementary colors, fonts, and font characteristics. In addition, you can set up entirely new color and font themes and apply them to your forms and reports. Creating a custom color theme is a great way to apply a company’s corporate color scheme to the forms and reports in an application.
With a form or report open in Design view, follow these steps:
FIGURE 37.13 Setting up a custom color theme
FIGURE 37.14 Selecting a theme element’s color
FIGURE 37.15 Applying a color theme to all matching objects in an application
Even after applying a color theme, you can adjust the colors of individual items on a report (or form, for that matter). Open the report in Design view, select the item to change, and choose its new color(s) in the Property Sheet.
Although not described or shown here, a similar dialog box is available (Create New Theme Fonts) in the Fonts drop-down list in the Themes group on the Design tab. The Create New Theme Fonts dialog box enables you to set up a custom font theme (heading and body fonts, and so on) to apply to forms and reports. Creating custom fonts themes works just like adding your own color themes to an application. Save the theme with a name you’ll recognize, and apply the font theme to forms and reports as needed.
Using the Print Preview view
Figure 37.16 shows a report in the Print Preview view. To change to this view, click the View down arrow (bottom half of the button) in the Views group of either the Home or Design tabs, and click Print Preview. This view displays your report with the actual fonts, shading, lines, boxes, and data that will be used on the report when printed to the default Windows printer. Clicking the left mouse button on the report’s surface changes the view to a page preview that shows the entire page.
FIGURE 37.16 Displaying a report in the zoomed Print Preview view
The Ribbon transforms to display controls relevant to viewing and printing the report. The Print Preview tab of the Ribbon includes controls for adjusting the size, margins, page orientation (Portrait or Landscape), and other printing options. The print options are stored with the report when you save the report’s design. The Print Preview tab also includes a Print button for printing the report, and another button for closing Print Preview and returning to the report’s previous view (Design, Layout, or Report view).
You can move around the page by using the horizontal and vertical scroll bars, or use the Page controls (at the bottom-left corner of the window) to move from page to page. The Page controls include DVD-like navigation buttons to move from page to page or to the first or last page of the report. You can also go to a specific page of the report by entering a value in the text box between the Previous and Next controls.
Right-clicking the report and selecting the Multiple Pages option, or using the controls in the Zoom group on the Print Preview tab of the Ribbon, lets you view more than one page of the report in a single view. Figure 37.17 shows a view of the report in the Print Preview’s two-page mode. Use the navigation buttons (in the lower-left section of the Print Preview window) to move between pages, just as you would to move between records in a datasheet. The Print Preview window has a toolbar with commonly used printing commands.
FIGURE 37.17 Displaying multiple pages of a report in Print Preview
If, after examining the preview, you’re satisfied with the report, click the Print button on the toolbar to print the report. If you’re dissatisfied with the design, select the Close button to switch to the Design view and make further changes.
Publishing in alternate formats
An important feature of the Print Preview tab is the ability to output the Access report in a number of common business formats, including PDF, XPS (XML Paper Specification), HTML, and other formats.
Clicking the PDF or XPS button in the Data group on the Print Preview tab opens the Publish as PDF or XPS dialog box (shown in Figure 37.18). This dialog box provides options for outputting in standard PDF format or in a condensed version (for use in a web context). You also specify the destination folder for the exported file.
FIGURE 37.18 Access 2013 provides powerful options for publishing reports.
The PDF or XPS view of an Access report is indistinguishable from the report when viewed in Access. Either format is common in many business environments these days.
Viewing the Report in Design view
Right-click the report’s title bar or tab and select Design View to show the report in Design view. As shown in Figure 37.19, the report design reflects the choices you made using the Report Wizard.
FIGURE 37.19 A report in Design view
Return to the Print Preview mode by choosing Report Tools Design ⇒ Views ⇒ View arrow ⇒ Print Preview.
Printing or viewing the report
The final step in the process of creating a report is printing or viewing it.
Printing the report
There are several ways to print your report:
- Choose File ⇒ Print in the main Access window (with a report highlighted in the Navigation pane). The standard Print dialog box appears. You use this dialog box to select the print range, number of copies, and print properties.
- Change to Print Preview view, and then click the Print button in the Print group of the Print Preview tab. The report is immediately sent to the default printer without displaying a Print dialog box.
Viewing the report
You can view a report in four different views: Design, Report, Layout, and Print Preview using the View drop-down list choices in the Views group of the Home tab or Design tab. You can also print a report to the default Windows printer.
The report Design view is one of two places where you create and modify reports. You began working with a new report by selecting a table or query to serve as the new report’s data source. Click the Blank Report button in the Reports group of the Create tab. By default, the new report appears in Layout view, as shown in Figure 37.20.
FIGURE 37.20 Layout view of a new blank report
Layout view enables you to see the relative positions of the controls on the report’s surface, as well as the margins, page headers and footers, and other report details.
The main constraint of Layout view is that you can’t make fine adjustments to a report’s design unless you put the report in Design view. Layout view is primarily intended to allow you to adjust the relative positions of controls on the report and is not meant for moving individual controls around on the report.
While in Layout view, you can also right-click any control and select Properties from the shortcut menu. The Property Sheet allows you to modify the default settings for the selected control.
Figure 37.21 shows the Ribbon while a report is open in Layout view. The Report Layout Tools offers four contextual tabs—Design, Arrange, Format, and Page Setup—that you can use to add and modify the controls for the reports. For example, use the Add Existing Fields button in the Tools group of the Design contextual tab to add the fields for the report.
FIGURE 37.21 The Ribbon offers four contextual tabs while a report is open in Layout view.
Saving the report
Save the report design at any time by choosing File ⇒ Save, File ⇒ Save As, or File ⇒ Export in Design view, or by clicking the Save button on the Quick Access Toolbar. The first time you save a report (or any time you select Save As or Export), a dialog box enables you to select or type a name.
Summary
Reports are an important part of most Access applications. Reports enable you to share data from the database with colleagues who don’t have access to the database itself or aren’t proficient in using a database. At this point you should be able to:
- Use the Report Wizard to create a report based on a table or query.
- Change between different report views.
- Change the report theme and colors in Design view.
- Preview, print, and save the report.