Create a grouped or summary report

Information is often easier to understand when it is divided into groups. For example, a report that groups sales by region can highlight trends that otherwise might go unnoticed. In addition, placing totals at the end of each group in your report can replace a lot of manual interaction with a calculator.

Microsoft Office Access 2007 makes working with grouped reports easy. You can create a basic grouped report by using the Report Wizard, you can add grouping and sorting to an existing report, or you can revise grouping and sorting options that have already been defined.

In this article


Understand grouping, report sections, and controls

If you're new to grouped reports, it helps to have some background on grouping and sorting, as well as the different report sections and types of controls. If you feel comfortable with these concepts already, you can skip this section and go to the section Build a new grouped report by using the Report Wizard.

About grouping and sorting

When you print a report, you usually want to organize the records in a particular order. For example, if you’re printing a list of suppliers, you might want to sort the records alphabetically by company name.

For many reports, sorting the records isn't enough. You may also want to divide them into groups. A group is a collection of records, along with any introductory and summary information displayed with the records, such as a header. A group consists of a group header, nested groups (if any), detail records, and a group footer.

Grouping allows you to separate groups of records visually and to display introductory and summary data for each group. For example, the following report groups sales by date shipped and calculates the total amount of sales for each day.

Grouped report with totals

Callout 1 The date introduces the group.
Callout 2 The total summarizes the group.

You can see how grouping works by comparing the List of Products by Category report (shown in the following illustration) to the datasheet for its underlying query, Product List. Both the report and the query sort products by category, but the report also prints the name of each category on a separate line at the beginning of each group (in the group header) and the number of products for each category on a separate line at the end of each group (in the group footer).

Products by Category report

Callout 1 The category name appears in every row in the datasheet.
Callout 2 The category name appears once for each group in the report, in the group header.
Callout 3 The total for the category appears at the end of each group, in the group footer.
Callout 4 The records appear as one large group in the datasheet.
Callout 5 The records for each category appear as a separate section in the report, preceded by a group header and followed by a group footer.

You can group on any fields and expressions you sort on (up to 10). You can group on the same field or expression more than once. When you group on more than one field or expression, Office Access 2007 nests the groups according to their group level. The first field you group on is the first and most significant group level; the second field you group on is the next group level; and so on. The following illustration shows how Office Access 2007 nests the groups.

Header Footer Hierarchy

Callout 1 Each group header is paired with a group footer.

You usually use a group header to display data that identifies the group in a separate section at the beginning of the group. You usually use a group footer to summarize the data in the group in a separate section at the end of the group.

Top of Page Top of Page

About the report sections

In Access, the design of a report is divided into sections. To create useful reports, you need to understand how each section works. For instance, the section in which you choose to place a calculated control determines how Access calculates the results. The following list shows a summary of the section types and their uses.

  • Report Header    Printed 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, or a title and date. The report header prints before the page header. When you place a calculated control in the report header, the value is calculated for the entire report. For example, placing a control that uses the Sum aggregate function in the report header calculates the sum for the entire report.
  • Page Header    Printed at the top of every page. Use a page header, for example, to repeat the report title on every page.
  • Group Header    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.
  • Detail    Printed once for every row in the record source. The Detail section is where you place the controls that make up the main body of the report.
  • Group Footer    Printed at the end of each group of records. Use a group footer to print summary information for a group.
  • Page Footer    Printed at the end of every page. Use a page footer to print page numbers or per-page information.
  • Report Footer    Printed 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, 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

About controls

Controls are objects that display data, perform actions, and let you view and work with information that enhances the user interface, such as labels and images. Access supports three types of controls: bound, unbound, and calculated. The following list shows a summary of these controls and their uses.

  • Bound control    A control whose source of data is a field in a table or query. You use bound controls to display values from fields in your database. The values can be text, dates, numbers, Yes/No values, pictures, or graphs. A text box is the most common type of bound control. For example, a text box in a form that displays an employee's last name might get this information from the Last Name field in the Employees table.
  • Unbound control    A control that doesn't have a source of data. You use unbound controls to display information, lines, rectangles, and pictures. For example, a label that displays the title of a report is an unbound control.
  • Calculated control    A control whose source of data is an expression rather than a field. You specify the value that you want in the control by defining an expression as the source of data for the control. An expression is a combination of operators (such as = and + ), control names, field names, functions that return a single value, and constant values. For example, the following expression calculates the price of an item with a 25 percent discount by multiplying the value in the Unit Price field by a constant value (.75).
= [Unit Price] * .75

An expression can use data from a field in the report's underlying table or query, or from a control on the report.

When you create a report, it's probably most efficient to add and arrange all the bound controls first, especially if they make up the majority of the controls on the report. You can then add the unbound and calculated controls that complete the design by using the tools in the Controls group of the Design tab.

Access Ribbon Image

You bind a control to a field by identifying the field from which the control gets its data. You can create a control that is bound to the selected field by dragging the field from the Field List pane to the report. (The Field List pane displays the fields of the report's underlying table or query. To display the Field List pane, do one of the following:

  • On the Design tab, in the Tools group, click Add Existing Field. Button image
  • Press ALT+F8.

When you double-click a field or drag it from the Field List pane to the report, you create a bound control.

Alternatively, you can bind a field to a control by typing the field name in the control itself or in the box for the ControlSource value in the control's property sheet. The property sheet defines the characteristics of the control, such as its name, the source of its data, and its format. To display the property sheet, press F4.

Using the Field List pane is the best way to create a bound control for two reasons:

  • A bound control has an attached label, and the label takes the name of the field (or the caption defined for that field in the underlying table or query) as its caption by default, so you don't have to type the caption yourself.
  • A bound control inherits many of the same settings as the field in the underlying table or query (such as for the Format, DecimalPlaces, and InputMask properties). Therefore, you can be sure that these properties for the field remain the same whenever you create a control that is bound to that field.

If you already have an unbound control and want to bind it to a field, set the control's ControlSource property to the name of the field.

Top of Page Top of Page

Build a new grouped report by using the Report Wizard

The Report Wizard presents you with a series of questions, and then generates a report based on your answers. Among those questions is one that asks for the field or fields to use to group your report. After the report is created, you can use it as-is or modify it to better suit your needs. Before you begin with the Report Wizard, you need to decide upon a data source.

Choose a record source

A report consists of information pulled from a table or query, and information stored with the report design, such as labels, headings, and graphics. The table or query that provides the underlying data is also known as the report's record source. Before you start creating your report, you should first think about the information you want to include. Among the fields you choose must be those you wish to group on or summarize. If the fields you want to include all exist in a single table, use that table as the record source. If the fields are pulled from more than one table, you'll want to use a query as the record source. That query may already exist in your database, or you may need to create a query specifically to fit the needs of your report.

To learn more about queries, follow the links in the See Also section of this article.

Start the Report Wizard

  1. On the Create tab, in the Reports group, click Report Wizard. Button image

Access starts the Report Wizard.

Field selection in report wizard

  1. Click the Tables/Queries drop-down list and choose the table or query that contains the fields you want on your report.
  2. Double-click fields in the Available Fields list to choose them.

Access moves them to the Selected Fields list. Alternatively, you can click the buttons located between the Available Fields box and the Selected Fields box to add or remove the selected field or to add all or remove all of the fields.

  1. If there are fields in another table or query that you also want to put on your report, click the Tables/Queries drop-down list again and choose the other table or query, and continue to add fields.
  2. After you've finished adding fields, click Next.

Group records in the Report Wizard

Grouping lets you organize and arrange records by group, such as by Region or SalesPerson. Groups can be nested so that you can easily identify the relationships among the groups and find the information you want quickly. You can also use grouping to calculate summary information, such as totals and percentages.

When you include more than one table in a report, the wizard examines the relationships between the tables and determines how you might want to view the information.

  1. On the page of the Report Wizard that asks Do you want to add any grouping levels?, click one of the field names in the list, and then click Next.
  2. To add grouping levels, double-click any of the field names in the list to add them to your report.

You can also remove a grouping level by double-clicking it in the page display on the right side of the dialog box. Use the arrow buttons to add and remove grouping levels, and adjust the priority of a grouping level by selecting it and clicking the up or down priority buttons. Access adds each grouping level and shows it nested within its parent grouping level.

Adding grouping levels in report wizard

  1. Click Grouping Options to display the Grouping Intervals dialog box.

Grouping intervals in report wizard

  1. Optionally, for each group-level field, choose a grouping interval.

The grouping interval lets you customize how records are grouped. In the previous illustration, records are grouped on the ShippedDate field, which is a Date/Time data type. The Report Wizard offers choices appropriate to the field type in the Grouping intervals list. Thus, because ShippedDate is a Date/Time type, you can choose to group by actual value (Normal), Year, Quarter, Month, Week, Day, Hour and Minute. If the field were a Text data type, you could choose to group by the entire field (Normal), or perhaps by the first one to five characters. For a numeric data type, you can choose to group by value (Normal), or by range in selected increments.

After selecting a grouping interval, click OK.

  1. Click Next to navigate to the next page of the wizard.

Sort and summarize records

You can sort records by up to four fields, in either ascending or descending order.

  1. Click the first drop-down list and choose a field on which to sort.

You can click the button to the right of the list to toggle between ascending and descending order (Ascending is the default). Optionally, click the second, third, and fourth drop-down lists to choose additional sort fields.

Sort orders in report wizard

  1. Click Summary Options if you want to summarize any of the numeric fields.

Note that the Summary Options button will only be visible if you have one or more numeric fields in the Detail section your report. The wizard displays the available numeric fields.

Summary options in report wizard

  1. Select the check box under your choice of Sum, Avg, Min or Max to include those calculations in the group footer.

You can also choose to show the details and summary or the summary only. In the latter case, totals for each ShippedDate value are shown (if you selected the check box for Sum, for example), but the order detail is omitted. You can also choose to show percent of total calculations for sums.

  1. Click OK.
  2. Follow the directions on the remaining pages of the Report Wizard. On the last page, you can edit the title of the report. This title will be displayed on the first page of the report, and Access will also save the report, using the title as the document name. You can edit both the title and the document name later.
  3. Click Finish. Access automatically saves the report and displays it in Print Preview, which shows you the report as it will look when printed.

You can use the navigation buttons at the bottom of the preview pane to view the pages of the report sequentially or jump to any page in the report. Click one of the navigation buttons or type the page number that you want to see in the page number box, and then press ENTER.

In Print Preview, you can zoom in to see details or zoom out to see how well data is positioned on the page. With the mouse pointer positioned over the report, click once. To reverse the effect of the zoom, click again. You can also use the zoom control in the status bar.

Top of Page Top of Page

Fine-tune your report

It is likely that the Report Wizard will not produce the exact results that you are looking for in your report. For example, you may want to add text, adjust column widths, add a logo, etc. Access provides two views in which you can make modifications to your report: Layout view and Design view. These are discussed briefly in this section.

About Layout view

Layout view is the most easy-to-use view for making report changes, and provides most of the tools you need to fine-tune your report. You can adjust column widths, rearrange columns, and add or modify grouping levels and totals. You can place new fields on the report design and set the properties for the report and its controls. The advantage of Layout view is that you can see your data while making changes to the report format, so you can immediately see how your changes affect the display of the data.

To open a report in Layout view, right-click the report in the Navigation Pane, and then click Layout view Button image.

You can use the property sheet to modify the properties for the report and its controls and sections. To display the property sheet, press F4.

You can use the Field List pane to add fields from the underlying table or query to your report design. To show the Field List pane:

  1. On the Format tab, in the Controls group, click Add Existing Fields Button image.

 Tip    Try Office 2010 Access 2010 includes new conditional formatting features that can greatly improve the readability of a report. Watch a video or try Office 2010!

About Design view

If you are unable to make a specific change to your report in Layout view, try Design view. Design view shows you the underlying structure of the report, and provides a few more design tools and capabilities than Layout view. For example, you can place a wider variety of controls on the report, you can adjust the alignment of controls more precisely, and you have the ability to set more properties than you do in Layout view.

To open a report in Design view, right-click the report in the Navigation Pane, then click Design View Button image.

Top of Page Top of Page

Add fields or other controls to an existing report

Every field, label, line, or picture you see on your report is created using some type of control. The most frequently-used control on a report is the text box. This is what is normally used to display data from the report's underlying record source. You can create text boxes that are already bound to fields in the record source by adding fields from the Field List pane, or you can add them to the report while working in Design view (but then you must "bind" them to fields in the record source yourself). The advantage of Design view is that you can add many other types of controls besides bound text boxes, such as rectangles, page breaks, and charts. This section describes both methods of adding controls to a report.

Add fields to an existing report

Layout view is the recommended view for adding fields to a report, but it can also be done in Design view. To add a field, first make sure that the Field List pane is displayed. The Field List pane contains a list of all of the fields that are available to be placed on the report. To display the Field List pane, press ALT+F8.

Add fields from the Field List pane

  • To add a single field, drag the field from the Field List pane to the section on the report where you want it displayed.
  • To add several fields at once, click the first field that you want, and then hold down CTRL and click each additional field that you want. Then, drag the selected fields onto the report.

When you drop the fields, Access creates a bound text box control for each field and automatically places a label control beside each field.

 Notes 

  • If you add a field from a related table, Access will add the appropriate grouping level for you.
  • If you add a field from another (non-related) table, Access will display a dialog box where you can specify how to relate the table to the existing data source for the report.

To see what table relationships have already been specified for your database, or to add new ones:

  1. On the Database Tools tab, in the Show/Hide group, click Relationships. Button image

For more information, see the article Create, edit or delete a relationship.

Add other controls to an existing report

When you add a field from the Field List pane to your report, a bound text box control for it is created automatically. Other controls such as labels, lines, and images can be added by switching to Design view and using the tools in the Controls group on the Design tab.

Access Ribbon Image

Determine the name of a tool

  • Place the mouse pointer over the tool.

Access displays the name of the tool.

Create a control by using the tools in the Controls group

  • Click the tool for the type of control that you want to add. For example, to create a check box, click the Check Box tool Button image.
  • Click in the report design where you want to position the upper-left corner of the control. Click once to create a default-sized control, or click and drag in the report to create a control of the size that you want.
  • If you don't position the control perfectly on the first try, you can move it by using the following procedure:
    • Click the control to select it.
    • Position the pointer over the control until it turns into a four-headed arrow Move pointer, and then drag the control to the desired location.

This process creates an "unbound" control. If the control is the type that can display data (a text box or check box, for example), you will need to enter a field name or expression in the Control Source property for the control before it will display any data.

Display the property sheet

  • Press F4.

 Note   When your new report is complete, be sure to save your work.

Top of Page Top of Page

Add or modify grouping and sorting in an existing report

If you have an existing report and you want to add sorting or grouping to it, or if you want to modify the report's existing sorting or grouping, this section helps you get started.

Add grouping, sorting, and totals in Layout view

You can perform simple sorting, grouping and totaling operations by right-clicking fields in Layout view and then choosing the operation you want from the shortcut menu. To switch to Layout view, right-click the report in the Navigation Pane and then click Layout view Button image.

 Note   Although the instructions in this section don't use the Group, Sort, and Total pane directly, it is a good idea to open the pane and observe how it changes as you work. You will get a better idea of what Access is doing and, as you get more comfortable working with the Group, Sort, and Total pane, you can use it to make additional adjustments to your report. To display the Group, Sort, and Total pane:

  1. On the Format tab, in the Grouping & Totals group, click Group & Sort. Button image

Sort on a single field

  • Right-click any value in the field that you want to sort.
  • On the shortcut menu, click the sort option you want. For example, to sort a text field in ascending order, click Sort A to Z Button image. To sort a numeric field in descending order, click Sort Largest to Smallest Button image.

Access sorts the report as you specified. If the Group, Sort, and Total pane is open, you can see that a new Sort by line for the field has been added.

Sort on multiple fields

 Note   When you apply sorting by right-clicking a field in Layout view, you can only sort one field at a time. Applying sorting to another field removes the sorting on the first field. This differs from the sorting behavior in forms, where multiple sort orders can be established by right-clicking each field in turn and the choosing the sort order you want. To create multiple sorting levels, see the section Add grouping, sorting, and totals by using the Group, Sort, and Total pane .

Group on a field

  • Right-click any value in the field on which you want to group.
  • On the shortcut menu, click Group On Button image.

Access adds the grouping level and creates a group header for it. If the Group, Sort, and Total pane is open, you can see that a new Group on line for the field is added.

Add a total to a field

This option lets you calculate a sum, average, count, or other aggregate for a field. A grand total is added to the end of the report, and group totals are added to any groups that exist on the report.

  • Right-click any value in the field that you want to total.
  • Click Total.
  • Click the operation you would like to perform: Sum, Average, Count Records (to count all records), Count Values (to count only the records with a value in this field), Max, Min, Standard Deviation, or Variance.

Access adds a calculated text box control to the report footer, which creates a grand total. Also, if your report has any grouping levels, Access adds group footers (if not already present) and places the total in each footer.

 Note   You can also add totals by clicking the field that you want totaled and then, on the Format tab, in the Grouping & Totals group, click Totals Button image.

Add grouping, sorting, and totals by using the Group, Sort, and Total pane

Working in the Group, Sort, and Total pane gives you the most flexibility when you want to add or modify groups, sort orders, or totals options on a report. Again, Layout view is the preferred view in which to work because it is much easier to see how your changes affect the display of the data.

Display the Group, Sort, and Total pane

  • In Layout view:

On the Format tab, in the Grouping & Totals group, click Group & Sort. Button image

  • In Design view:

On the Design tab, in the Grouping & Totals group, click Group & Sort. Button image

Access displays the Group, Sort, and Total pane.

Group, Sort, and Total pane

To add a new sorting or grouping level, click Add a group or Add a sort.

A new line is added to the Group, Sort, and Total pane, and a list of available fields is displayed.

Field list in Group, Sort, and Total pane

You can click one of these field names or you can click expression below the list of fields to enter an expression. Once you choose a field or enter an expression, Access adds the grouping level to the report. If you are in Layout view, the display changes immediately to show the grouping or sort order.

 Notes 

  • If there are already several sorting or grouping levels defined, you may need to scroll down in the Group, Sort, and Total pane before you can see the Add a group and Add a sort buttons.
  • You can define up to 10 grouping and sorting levels in a report.

Change grouping options

Each sorting or grouping level has a number of options that can be set to obtain the results you want.

Grouping options

  • To display all the options for a grouping or sorting level, click More on the level that you want to change.

Grouping options (expanded)

  • To hide the options, click Less.

Sort order    You can change the sort order by clicking the sort order drop-down list, then clicking the option you want.

Group interval    This setting determines how the records are grouped together. For example, you can group on the first character of a text field so that all that start with "A" are grouped together, all that start with "B" are grouped together, and so on. For a date field, you can group by day, week, month, quarter, or you can enter a custom interval.

Totals    To add totals, click this option. You can add totals on multiple fields, and you can do multiple types of totals on the same field.

Totals pop-up window

  • Click the Total On drop-down arrow and select the field you want to have summarized.
  • Click the Type drop-down arrow and select the type of calculation to perform.
  • Select Show Grand Total to add a grand total to the end of the report (in the report footer).
  • Select Show group totals as % of Grand Total to add a control to the group footer that calculates the percentage of the grand total for each group.
  • Select Show in group header or Show in group footer to display the total in the desired location.

Once all the options have been chosen for a field, you can repeat the process and summarize another field by selecting the other field from the Total On drop-down list. Otherwise, click outside the Totals pop-up window to close it.

Title    This allows you to change the title of the field being summarized. This is used for the column heading and for labeling summary fields in headers and footers.

To add or modify the title:

  • Click the blue text following with title.

The Zoom dialog box appears.

  • Type the new title in the dialog box, and then click OK.

With/without a header section    Use this setting to add or remove the header section that precedes each group. When adding a header section, Access moves the grouping field to the header for you. When you remove a header section that contains controls other than the grouping field, Access asks for confirmation to delete the controls.

With/without a footer section    Use this setting to add or remove the footer section that follows each group. When you remove a footer section that contains controls, Access asks for confirmation to delete the controls.

Keep group together    This setting determines how groups are laid out on the page when the report is printed. You may want to keep groups together as much as possible to reduce the amount of page turning that is needed to see the entire group. However, this usually increases the amount of paper needed to print the report, because most pages will have some blank space at the bottom.

  • Do not keep group together on one page    Use this option if you are not concerned about groups being broken up by page breaks. For example, a group of 30 items may have 10 items on the bottom of one page and the remaining 20 items at the top of the next page.
  • Keep whole group together on one page    This option helps minimize the number of page breaks in a group. If a group cannot fit in the remaining space on a page, Access leaves that space blank and begins the group on the next page instead. Large groups may still span multiple pages, but this option minimizes the number of page breaks within the group as much as possible.
  • Keep header and first record together on one page    For groups with group headers, this ensures that the group header will not print by itself at the bottom of a page. If Access determines that there is not enough room for at least one row of data to be printed after the header, the group begins on the following page.

Change the priority of grouping and sorting levels

To change the priority of a grouping or sorting level, click the row in the Group, Sort, and Total pane and then click the up arrow or the down arrow on the right side of the row.

Delete grouping and sorting levels

To delete a grouping or sorting level, click the row you want to delete in the Group, Sort, and Total pane, and then press DELETE or click the Delete button on the right side of the row. When you delete a grouping level, if the grouping field was in the group header or footer, Access moves it to the report's Detail section. Any other controls that were in the group header or group footer are deleted.

Top of Page Top of Page

Create a summary report (without record details)

If you want to show only totals (that is, just the information in header and footer rows), on the Format tab, (or on the Design tab in Design view), in the Grouping & Totals group, click Hide Details. Doing this hides the records at the next lower level of grouping, resulting in a much more compact presentation of the summary data. Although the records are hidden, the controls in the hidden section are not deleted. Click Hide Details again to restore the Detail rows to the report.

Create a grouping effect without using headers

You can achieve a grouping effect on your report without the use of headers. Removing headers creates a more compact report because less vertical space is used by the header lines.

The general procedure involves removing the header for each group, which moves each grouping field into the Detail section of the report. To restore the visual grouping effect, you set the Hide Duplicates property of each grouping field to Yes.

Example

Consider this typical grouped report that uses group headers:

Grouped report with group headers

As you can see, each company name takes up an entire line, as does each order ID.

In this example, you will edit the report so that it looks like the following illustration:

Grouped report with group headers removed

  1. Open the report in Layout view by right-clicking it in the Navigation Pane, and then clicking Layout View Button image.
  1. On the Format tab, in the Grouping & Totals group, click Group & Sort. Button image

 Note    It is a good idea to start with the lowest-level group and work your way up; otherwise, the display of the report can become confusing while you are working.

  1. Click the lowest grouping level in the list, and then click More.
  2. From the header section drop-down list, select without a header section.

When you remove a header, Access preserves the text box for the grouping field by moving it to the Detail section. However, any other controls in the group header might be deleted. Access warns you if any controls are going to be deleted as a result of this action. If there are controls in the group header that you want to preserve, be sure to move them to another report section before performing this step. You will need to switch to Design view to move the controls to other sections.

The resulting report is shown in the following illustration. Notice that the first OrderID is on the same line as the first detail record, instead of on its own line.

Grouped report with OrderID header removed

Unfortunately, the OrderID repeats on every detail line, and this makes it hard to see exactly how many orders there are. At this point, setting the text box control's Hide Duplicates property is useful. Follow these steps to set this property:

  • Select one of the repeating values.
  • If the property sheet is not currently displayed, press F4 to display it.
  • On the Format tab of the property sheet, click the drop-down list for Hide Duplicates and then click Yes.

The duplicate order IDs are hidden. Note that you can see how many orders there are, and how many products there are per order.

Grouped report with duplicate OrderIDs hidden

  1. For this example, we repeat step 4 for the Company Name grouping level. The final report looks like the following illustration:

Grouped report with group headers removed

The Company Name value is now on the same line as the first detail record, and the Hide Duplicates property prevents the Company Name value from repeating on every line. For a long report that uses several grouping levels, this can result in a significant paper savings.

Top of Page Top of Page

Save, print, or send your report

After you save your report design, you can use it multiple times. The report's design stays the same, but you see current data every time you print the report. If your reporting needs change, you can modify the report design or create a new, similar report that is based on the original.

Save your report

  1. Click the Microsoft Office Button Button image, and then click Save, or press CTRL+S Button image.

Alternatively, you can click Save Button image on the Quick Access Toolbar.

  1. If the report is untitled, type a name in the Report Name box and then click OK.

Save your report design under a new name

  1. Click the Microsoft Office Button Button image, and then click Save As Button image.
  1. In the Save As dialog box, type a name in the Save Report To box, click Report in the As box, and then click OK.

Print your report

You can print a report from Print Preview, Layout view, Design view, Report view, or from the Navigation Pane. Before you print, you should double check page settings, such as margins and page orientation. Access saves the page settings with the report, so you need to set them only once. You can save the settings later, if your needs change.

Change the page settings

  1. Open the report in any view.
  2. On the Page Layout tab, in the Page group, click an option:
    • Size Button image allows you to select from a number of paper sizes, or to enter a custom paper size.
    • Portrait Button image orients the page vertically.
    • Landscape Button image orients the page horizontally.
    • Margins Button image allows you to select from several predefined margin widths, or you can click Advanced to enter custom margin widths by using the Page Setup dialog box.
    • Show Margins toggles the display of margins on and off (Layout view only).
    • Columns displays the Page Setup dialog box. Enter the number of columns you want, set your desired column size, and then click OK.
    • Print Data Only suppresses the printing of any labels you have placed on your report. Only the controls that are bound to data in the underlying table or query will be printed. This feature can be helpful on reports that you occasionally print on pre-printed forms. You can suppress the printing of labels in those situations, and then enable the printing of labels when you are printing the report on blank paper.

 Note   To use more detailed page settings, click the Page Setup button at the lower right corner of the Page Layout group to display the Page Setup dialog box. You can use the Page Setup dialog box to adjust all the settings that are available on the Page Layout tab, and also to select a specific printer for the report.

Send your report to a printer

  1. Open the report in any view, or just select the report in the Navigation Pane.
  1. Click the Microsoft Office Button Button image, and then click Print. Button image
  1. In the Print dialog box, enter your choices for options such as printer, print range, and number of copies.
  2. Click OK.

Top of Page Top of Page

Send your report as an e-mail message

You can send your report to users as an e-mail message instead of printing a paper copy.

  1. In the Navigation Pane, click the report to select it, click the Microsoft Office Button Button image, and then click E-mail Button image.
  2. In the Send Object As dialog box, click the file format that you want to use.

Send Object As dialog box

  1. Complete any remaining dialog boxes.
  2. When your e-mail program appears, type the message details and send the message.

Top of Page Top of Page

Remarks

A problem can arise if you have a report in an .accdb file that sorts or groups on a lookup field, and you want to import that report into an .mdb file and then open it with an earlier version of Access.

The internal method by which lookup fields are sorted and grouped in .accdb files is incompatible with .mdb files. If you create a report in an .accdb file that uses grouping or sorting on a lookup field, and you then export it into an .mdb file (or use the Save As command to save the database as an .mdb file) and then try to open the report with an earlier version of Access, the report will fail to load.

To revise the report so that you can import it into an .mdb file and open it in an earlier version of Access, do the following:

  1. In the .accdb file, create a copy of the report.
  2. Open the copy of the report in Layout view.
  3. If the Group, Sort, and Total pane is not displayed:
  1. On the Format tab, in the Grouping & Totals group, click Group & Sort. Button image
  1. Delete all the lines in the Group, Sort, and Total pane by clicking the Delete button to the right of each line.
  • Click the Microsoft Office Button Button image, and then click Save, or press CTRL+S Button image.
  • Click the Microsoft Office Button Button image, and then click Close Database. Button image
  • Click the Microsoft Office Button Button image, and then click Open. Button image
  1. Browse to the .mdb file into which you want to import the report, and then click Open.
  2. Import the report copy into the .mdb file.

ShowHow?

  1. On the External Data tab, in the Import group, click Access. Button image
  1. Click Browse.
  2. Browse to the .accdb file containing the report, and click Open.
  3. You are not importing any tables at this time, so it does not matter whether you choose to import or link to the source data. Click OK to continue.
  4. In the Import Objects dialog box, click the Reports tab.
  5. Click the report you want to import, and then click OK.
  6. If the import operation is successful, Access asks if you want to save the import steps for later use. If you want to save the steps, select the Save import steps check box, enter the requested information, and click Save Import. If you do not want to save the steps, click Close.

Access imports the report and displays it in the Navigation Pane.

  1. Open the report in Layout view.
  2. If the Field List pane is not displayed:
  1. On the Format tab, in the Controls group, click Add Existing Fields Button image.
  1. In the Fields available in related tables pane, find the table that contains the field you want displayed on the report. This field will contain the same values that you currently see displayed by the lookup field on the report.
  2. Drag the field to the report.

You now have two columns displaying the same information.

  1. Right-click the display field you just added, and click the sorting option you want to use.
  2. Delete the display field that you just added and sorted.
  3. If the Group, Sort, and Total pane is not displayed:
  1. On the Format tab, in the Grouping & Totals group, click Group & Sort. Button image
  1. Click Add a group.
  2. In the Field/Expression pop-up window, click the name of the lookup field on the report.

The report is now sorted and grouped as it was in the .accdb file. Save and close the report.

Top of Page Top of Page

 
 
Applies to:
Access 2007