Introduction to reports

When you use a database, you typically use reports to view, format, and summarize data. For instance, you might create a list-type report to display phone numbers for all your contacts, or a summary report to total up the sales for your company across different regions and time periods.

This article provides an overview of reports in Microsoft Access 2010, and introduces new or changed report features. This article also explains how to create a report, how to sort, group, and summarize data, and how to preview and print a report.

You’ll find it’s much easier to create meaningful reports if your database has a well-designed table structure and relationships. For an introduction to planning and designing a database, see the article Database design basics.

In this article


Overview

A report is a database object that you use to display and summarize data. Reports provide a way to distribute or archive snapshots of your data, either by being printed out, converted to PDF or XPS files, or exported to other file formats.

Employee report in Print Preview

Reports can provide details about individual records, summaries across many records, or both. You can also use Access reports to create labels for mailings or other purposes.

It is possible to create “unbound” reports that do not display data, but for the purposes of this article, we’ll assume that a report is bound to a data source such as a table or query.

Client reports vs. Web reports

Access 2010 provides a new feature that allows you to create a “Web database” by publishing an Access database to a SharePoint server that is running Access Services. When you create a Web database, Access reports are rendered in the browser using SQL Server Reporting Services. This conversion places some limitations on the features you can use in reports that you want to render in the browser. However, if you are not concerned about the report rendering in the browser, you can use the full set of features that the Access report designer provides.

Parts of a report

In Access, the design of a report is divided into sections. In a client database, you can view your report in Design view to see its sections. In Layout view, the sections are not as obvious, but they are still there, and can be selected by using the drop-down list in the Selection group on the Format tab. To create useful reports, you need to understand how each section works. For example, the section in which you choose to place a calculated control determines how Access calculates the results. The following list is a summary of the section types and their uses:

  • Report Header   This section is printed just once, at the beginning of the report. Use the report header for information that might normally appear on a cover page, such as a logo, a title, or a date. When you place a calculated control that uses the Sum aggregate function in the report header, the sum calculated is for the entire report. The report header is printed before the page header.
  • Page Header   This section is printed at the top of every page. For example, use a page header to repeat the report title on every page.
  • Group Header   This section is printed at the beginning of each new group of records. Use the group header to print the group name. For example, in a report that is grouped by product, use the group header to print the product name. When you place a calculated control that uses the Sum aggregate function in the group header, the sum is for the current group. You can have multiple group header sections on a report, depending on how many grouping levels you have added. For more information about creating group headers and footers, see the section Add grouping, sorting, or totals.
  • Detail   This section is printed once for every row in the record source. This is where you place the controls that make up the main body of the report.
  • Group Footer   This section is printed at the end of each group of records. Use a group footer to print summary information for a group. You can have multiple group footer sections on a report, depending on how many grouping levels you have added.
  • Page Footer   This section is printed at the end of every page. Use a page footer to print page numbers or per-page information.
  • Report Footer   This section is printed just once, at the end of the report. Use the report footer to print report totals or other summary information for the entire report.

 Note    In Design view, the report footer appears below the page footer. However, in all other views (Layout view, for example, or when the report is printed or previewed), the report footer appears above the page footer, just after the last group footer or detail line on the final page.

Top of Page Top of Page

What’s new

Creating a report in Access 2010 is a very similar process to creating a report in Access 2007. However, in Access 2010 there are a few new features that pertain to reports:

  • Shared Image Gallery   In Access 2010, you can now attach an image to a database, and then use the image across multiple objects. Updating the single image updates it wherever it is used across the entire database.
  • Office Themes   In Access 2010, you can now use standard Microsoft Office Themes to apply professionally-designed sets of fonts and colors to all of your Access forms and reports at once.
  • More powerful conditional formatting   Access 2010 includes more powerful tools for highlighting data on a report. You can add up to 50 conditional formatting rules for each control or group of controls, and in client reports, you can add data bars to compare data across records.
  • More flexible layouts   In Access 2010, the default design method for reports is to place controls in layouts. These grids help you align and size controls easily, and are required for any reports that you want to render in a browser. Although layouts themselves are not new in Access 2010, there are some changes to the ways in which you use them to move, align, and resize controls. For more information, see the video Introduction to form and report layouts.

Top of Page Top of Page

Create a new report

Step 1: Choose a record source

The record source of a report can be a table, a named query, or an embedded query. The record source must contain all of the rows and columns of data you want display on the report.

  • If the data you want is contained in an existing table or query, select the table or query in the Navigation Pane, and then continue to Step 2: Choose a report tool.
  • If the record source does not yet exist, do one of the following:

For more information about creating tables or queries, see the articles Introduction to tables or Introduction to queries.

For more information about creating and selecting record sources, see the article Set the record source for a report.

Step 2: Choose a report tool

The report tools are located on the Create tab of the ribbon, in the Reports group. The following table briefly describes what each tool does:

Web-compatible reports

Reports created by using these tools are compatible with the Publish to Access Services feature, and will render in a browser. Note that Web compatible objects are indicated by the globe on the object’s icon.

Button image Tool Description
Button image

Report

Creates a simple, tabular report containing all of the fields in the record source you selected in the Navigation Pane.
Button image

Blank Report

Opens a blank report in Layout view, and displays the Field List task pane. Access creates a record source query as you drag fields from the Field List to the report.

For more information about the Publish to Access Services feature, see the article Build an Access database to share on the Web.

Client reports

Reports created by using these tools are not compatible with the Publish to Access Services feature. Adding client reports to a Web database will not prevent the database from publishing, but the client reports will not be available in the browser. However, you can use a client report when the database is open in Access.

Button image Tool Description
Button image

Report

Creates a simple, tabular report containing all of the fields in the record source you selected in the Navigation Pane.
Button image

Report Design

Opens a blank report in Design view, to which you can add just the fields and controls you want.
Button image

Blank Report

Opens a blank report in Layout view, and displays the Field List task pane. As you drag fields from the Field List to the report, Access creates an embedded query and stores it in the Record Source property of the report.
Button image

Report Wizard

Displays a multiple-step wizard that lets you specify fields, grouping/sorting levels, and layout options. The wizard creates a report based on the selections you make.
Button image

Labels

Displays a wizard that lets you select standard or custom label sizes, as well as which fields you want to display, and how you want them sorted. The wizard creates the label report based on the selections you make.

Step 3: Create the report

  1. Click the button for the tool you want to use. If a wizard appears, follow the steps in the wizard and click Finish on the last page.

    Access displays the report in Layout view.
  2. Format the report until it looks the way you want:
  • Resize fields and labels by selecting them and then dragging the edges until they are the size you want.
  • Move a field by selecting it (and its label, if present), and then dragging it to the new location.
  • Right-click a field and use the commands on the shortcut menu to merge or split cells, delete or select fields, and perform other formatting tasks.

In addition, you can use the features described in the following sections to make your report more attractive and readable.

Top of Page Top of Page

Add grouping, sorting, or totals

The fastest way to add grouping, sorting, or totals to a report is to right-click the field to which you want to apply the group, sort, or total, and then click the desired command on the shortcut menu.

You can also add grouping, sorting, or totals by using the Group, Sort, and Total pane while the report is open in Layout view or Design view:

  1. If the Group, Sort, and Total pane is not already open, on the Design tab, in the Grouping and Totals group, click Group & Sort.
  2. Click Add a group or Add a sort, and then select the field on which you want to group or sort.
  3. Click More on a grouping or sorting line to set more options and to add totals.

For more detailed information about grouping, sorting, and totals, see the article Create a grouped or summary report.

Top of Page Top of Page

Highlight values by using conditional formatting

Access 2010 includes more powerful tools for highlighting data on a report. You can add up to 50 conditional formatting rules for each control or group of controls, and in client reports, you can add data bars to compare data across records.

To add conditional formatting:

  1. Open the report in Layout view by right-clicking the report in the Navigation Pane and then clicking Layout View.
  2. Select all of the controls to which you want to apply the conditional formatting. To select multiple controls, hold down the SHIFT or CTRL key and click the controls you want.
  3. On the Format tab, in the Control Formatting group, click Conditional Formatting.

    Access opens the Conditional Formatting Rules Manager dialog box.
  4. In the Conditional Formatting Rules Manager dialog box, click New Rule.
  5. In the New Formatting Rule dialog box, select a value under Select a rule type:
  • To create a rule that is evaluated for each record individually, select Check values in the current record or use an expression.
  • To create a rule that compares records to each other by using data bars, click Compare to other records.

 Note    The Compare to other records option is not available in Web databases.


  1. Under Edit the rule description, specify the rule that will determine when the formatting should be applied, as well as the formatting that you want when the rule’s criteria are met.
  2. Click OK to return to the Conditional Formatting Rules Manager dialog box.
  3. To create an additional rule for this control or set of controls, repeat this procedure from step 4. Otherwise, click OK to close the dialog box.

For more information about adding conditional formatting to a report, see the video Use conditional formatting on reports.

Top of Page Top of Page

Get a professional look with Themes

You can now apply Office 2010 Themes to an Access database, helping you to create a consistent style across all your Office documents.

 Important    If you choose an Office Theme, font, or color, it is applied to all the forms and reports in your database (not just the one you are working on).

  1. Open a report in Layout view by right-clicking it in the Navigation Pane and then clicking Layout View.
  2. On the Design tab, in the Themes group, select the Theme, color or font that you want:


  • Use the Theme gallery to simultaneously set colors and fonts to a pre-designed scheme.
  • Use the Colors or Fonts galleries to set colors or fonts independently.


For more information about using Office Themes, see the video Make your Access forms and reports more consistent and appealing with Office Themes.

Top of Page Top of Page

Add images   

Traditionally in Access, images have been bound to individual image controls on forms or reports. To change a commonly-used image that was used across multiple forms and reports, each image control had to be edited manually. In Access 2010, however, you can now attach an image to a database once, and then use the image across multiple objects. Updating the single image updates it wherever it is used across the entire database. This is very useful for such things as company logos or background images that are used throughout a database.

Add an image

  1. In the Navigation Pane, right-click the report to which you want to add the image, and then click Layout View.
  2. Click on the report where you want to add the image.
  3. On the Design tab, in the Controls group, click Insert Image.
  4. Do one of the following:
  • Use an existing image   If the image you want is already in the gallery, click it to add it to the report.
  • Upload a new image   At the bottom of the gallery, click Browse. In the Insert Picture dialog box, navigate to the image you want to use, and then click Open.

Access adds the selected image to the report.

Add a background image

 Note    Background images cannot be added to Web-compatible reports.

  1. In the Navigation Pane, right-click the report to which you want to add the background image, and then click Layout View.
  2. On the Format tab, in the Background group, click Background Image.
  3. Do one of the following:
  • Use an existing image   If the image you want is already in the gallery, click it to add it to the report.
  • Upload a new image   At the bottom of the gallery, click Browse. In the Insert Picture dialog box, navigate to the image you want to use, and then click Open.

Access adds the selected image to the report.

Top of Page Top of Page

Preview and print a report

Preview a report

  1. Open the report you want to preview, or just select it in the Navigation Pane.
  2. On the File tab, click Print, and then click Print Preview.

    Access opens the report in Print Preview. You can use the commands on the Print Preview tab to do any of the following:
  • Print the report;
  • Adjust page size or layout;
  • Zoom in or out, or view multiple pages at a time;
  • Refresh the data on the report;
  • Export the report to another file format.
  1. To return to the database workspace, on the Print Preview tab, in the Close Preview group, click Close Print Preview.

Print a report

In addition to printing from Print Preview, you can also print a report without previewing:

  1. Open the report you want to preview, or just select it in the Navigation Pane.
  2. On the File tab, click Print.
  • To send the report directly to the default printer without setting printer options, click Quick Print.
  • To open a dialog box where you can select a printer, specify the number of copies, and so on, click Print.

Top of Page Top of Page

Try Office 2010 today!

 
 
Applies to:
Access 2010