Access 2010

Advanced Report Options

Introduction

Access 2010

Access 2010 offers several advanced options for creating and modifying reports. The Report Wizard is a tool that guides you through the process of creating complex reports. Once you've created a report—whether through the Report Wizard or the Report command—you can then format it to make it look exactly how you want.

In this lesson, you'll learn how to use the Report Wizard to create complex reports. You'll also learn how to use Access's formatting options to format text , change report colors and fonts , and add a logo .

We will be showing you how to create and modify reports with examples from our sample database. If you would like to follow along, download our example and use it to follow the procedures demonstrated in this lesson.

The Report Wizard

While using the Report command is a quick way to create reports from the current object, it's not as helpful if you want to create a report with data from multiple objects. The Report Wizard makes it easy to create reports using fields from multiple tables or queries. It even lets you choose how your data will be organized .

To create a report using the Report Wizard:

  1. Select the Create tab and locate the Reports group. Click the Report Wizard command.
    The Report Wizard command The Report Wizard command
  2. The Report Wizard dialog box will appear.
    The Report Wizard dialog box The Report Wizard dialog box

Step 1: Select the fields to include in your report

  1. Click the drop-down arrow to select the table or query that contains the desired field or fields.
    Selecting a table that contains fields to include in the report Selecting a table that contains fields to include in the report
  2. Select a field from the list on the left, and click the right arrow to add it to the report.
    Adding fields to the report Adding fields to a report
  3. You can add fields from more than one table or query by repeating the above steps. Once you've added the desired fields, click Next .
    The added fields The added fields

Step 2: Organize the report

The Report Wizard will provide you with options that let you choose how to view and organize your data. These options group like data within your fields and organize these fields into multiple levels, like in an outline or bulleted list.

If you are building a report from only one table or query, you can skip to No. 3 in this section.

  1. Access will offer a list of several organization options. Select an option from the list to preview it.
    Grouping options Grouping options
  2. Click Next when you are satisfied with the basic organization of your data.
  3. If you're not satisfied with the way your data is organized, you can now modify the grouping levels.
    • Select a field from the list, and click the right arrow to add it as a new level.
      Adding a new grouped level Adding a new grouped level
    • If necessary, modify the order of your grouped fields by selecting a field and clicking the up or down Priority arrow to move it up or down a level.
      Moving the Sales Unit field down a grouping level Moving the Sales Unit field down a grouping level
  4. Once you are satisfied with the organization of your report, click Next .

Step 3: Sort your report data

  1. Click the top drop-down arrow, and select the name of the first field you want to sort.
  2. Click the button on the right to change the sort to ascending or descending .
    Sorting the report Sorting the report
  3. Add any additional sorts. You may sort up to four fields . The sort will be applied from top to bottom, meaning the sort at the top of the list will be the main sort.
  4. When you are satisfied with the way your data is sorted, click Next .

Depending on the grouping you have chosen for your data, your sorting options may be limited.

Step 4: Select a layout and title

  1. Click the various layout options to see how they look, then select one to use in your report.
  2. Select either a portrait (tall) or landscape (wide) orientation for your report.
    Setting the report layout Setting the report layout
  3. Once you are satisfied with your report layout, click Next .
  4. Place your cursor in the text box and type the title you want for your report.
  5. Select whether you want to preview the report or modify its design, then click Finish .
    Finishing the report Finishing the report
  6. Your report will be created and saved.

Just as with other reports, you may have to adjust your field and row size and location to make sure all of your data fits the way you want it to.

Formatting reports

One of the strengths of reports is that you can modify their appearance to make them look how you want. You can add headers and footers to your report, apply new colors to the layout, and even add a logo . All of these things can help you create visually appealing reports.

Modifying report text

The bulk of the information in your report comes straight from the query or table you built it from, which means you can't edit it within the report. However, you can change, add, or delete label text, headers, and footers to make your report clearer and easier to read. For example, in our report, we decided that we didn't need the field headings to understand our data, so we deleted them.

When working with text and text boxes in reports, you can use many of the text and shape formatting tools you use in other Office programs to modify color, font, and other settings. If you're not sure how to perform basic text and shape formatting, you can read the Formatting Text and Working With Shapes lessons from our Word 2010 tutorial.

Our report, with simple text formatting applied The report, with simple text formatting applied

Modifying the page header and footer

To view and modify the header and footer that appear on every page of your report, select the View command on the Ribbon and switch to Design view . The header and footer are located in the white space beneath the Page Header and Page Footer bars.

The page header and footer in Design View The page header and footer in Design View

Depending on your report's design, sometimes you may find that there is no white space in the page header and footer, as in the image above. If this is the case, you must resize the header and footer before you can add anything to them. Simply click and drag the bottom border of the header or footer to make it larger.

Resizing the page header Resizing the page header

To add text to a header or footer:

  1. On the Ribbon, select the Design tab in the Report Design Tools group and locate the Controls group.
  2. Select the Label command.
    The Label command The Label command
  3. Place your cursor in the white space in your header or footer, and click and drag to create your label. Release the mouse when it is the desired size.
    Clicking and dragging to create a label Clicking and dragging to create a label
  4. Place your cursor in the text box, click once, and type the desired text.
    Adding text to the label Adding text to the label

To add the date and time to a header or footer:

  1. On the Ribbon, select the Design tab in the Report Design Tools group and locate the Header/Footer group.
  2. Select the Date and Time command.
    The Date & Time command The Date and Time command
  3. The Date and Time dialog box will appear. Select the desired formatting options. A preview of the text that will be included in your report will appear. When you are satisfied with the appearance of the date and time, click OK .
    Selecting options in the Date and Time dialog box Selecting the date and time format in the Date and Time dialog box

By default, the date and time appear in the header . If you want to move them to the footer instead, click the date and time boxes and drag them to the desired location.

To add page numbers to a header or footer:

  1. On the Ribbon, select the Design tab in the Report Design Tools group and locate the Header/Footer group.
  2. Select the Page Numbers command.
    The Page Numbers command The Page Numbers command
  3. The Page Numbers dialog box will appear. Select the format of the page numbers.
    • Select Page N to display the number of only the current page.
    • Select Page N of M to display the number of the current page and the number of total pages.
  4. Select whether to put your page numbers in the header or footer.
  5. Click the drop-down arrow to select the alignment of the page numbers.
    Selecting page number settings in the Page Numbers dialog box Selecting page number settings in the Page Numbers dialog box
  6. When you are satisfied with the settings, click OK .

Enhancing report appearance

To add a logo:

  1. On the Home tab of the Ribbon , click the View command and select Layout View from the drop-down list.
    Switching to Layout View Switching to Layout View
  2. Select the Design tab and locate the Header/Footer group, then select the Logo command.
    The Logo command The Logo command
  3. The Insert Picture dialog box will appear. Locate and select the desired image file and click OK to add it to your report.
    Inserting a logo Inserting a logo
  4. A shrunken version of your image will appear in the header. Click and drag the image border to resize it.
    Resizing the logo Resizing the logo
  5. If necessary, move your logo to the desired location by clicking and dragging it.
    The resized, repositioned logo The resized, repositioned logo

Themes and fonts

A theme is a set of colors and fonts that applies to the entire database to give it a consistent, professional look. By default, databases use the Office theme. When you change the theme, all of the theme fonts and colors in your database change to match the new theme. Designing and modifying reports using theme elements can help you keep the appearance of your reports consistent.

To change the theme:

  1. Select the Design tab and locate the Themes group.
  2. Click the Themes command.
    The Themes command The Themes command
  3. A drop-down menu will appear. Hover the mouse over a theme to see a live preview of it.
    Hovering to preview a new theme Hovering to preview a new theme
  4. Select the desired theme. The theme will be applied to your entire database.
    The report, now with a new theme The report, now with a new theme

To change the theme fonts:

  1. Select the Design tab and locate the Themes group.
  2. Click the Fonts command.
    The Fonts command The Fonts command
  3. A drop-down menu will appear. Select a set of theme fonts .
    Selecting a new set of theme fonts Selecting a new set of theme fonts
  4. The fonts will be applied to your entire database.
    The report, now with new fonts The report, now with new fonts

Challenge!

  1. If you haven't already, download our sample database and open it.
  2. Open the December Orders report.
  3. Add the date and time to the header .
  4. Add page numbers to the footer .
  5. Select the report title and edit it to say December Orders .
  6. Choose a new theme and theme fonts .
  7. Add a logo . You can use an image from your computer or download our logo .