Create a simple report

You can create a variety of different reports in Microsoft Access 2010, ranging from the simple to the complex. This article describes the different tools available for creating reports, and for which situations each one is recommended.

Before you begin

The primary criteria for choosing a report building tool is whether you are building a database to share on the web, and if so, whether you want the report to render in the browser. When you publish a database to a SharePoint server that is running Access Services, reports are converted into a special format that renders in browser. The only report tools that are compatible with this feature are the Report and Blank Report tools. However, if you are working in a client database (or if you are working in a web database but don't intend to display the report in the browser), you can use any of the report tools that Access provides.

For more information about web databases, see the article Build an Access database to share on the Web.

Top of Page

Choose a record source

A report consists of information that is pulled from tables or queries, as well as information that is stored with the report design, such as labels, headings, and graphics. The tables or queries that provide the underlying data are also known as the report's record source. If the fields that you want to include all exist in a single table, use that table as the record source. If the fields are contained in more than one table, you need to use one or more queries as the record source. Those queries may already exist in your database, or you may need to create new queries specifically to fit the needs of your report.

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

Top of Page

Create a report: client- and web-compatible report tools

The following sections describe the tools that can be used to create both client- and web-compatible reports. When you are working in a web database, the globe symbol on the report tool indicates that the resulting report will be web-compatible.

Create a report by using the Report tool

The Report tool provides the fastest way for you to create a report, because it generates a report immediately without prompting you for information. The report displays all the fields from the underlying table or query. The Report tool may not create the final, polished product that you ultimately want, but it is quite useful as a means to quickly look at the underlying data. You can then save the report and modify it so that it better serves your purposes.

In the Navigation Pane, select the table or query that will serve as the record source for the report.
On the Create tab, in the Reports group, click Report.

Button image -OR-

Access builds the report and displays it in Layout view.

After viewing the report, you can save it and then close both the report and the underlying table or query that you used as a record source. The next time that you open the report, Access will display the most recent data from your record source.

Create a report by using the Blank Report tool

If you want to be more selective about the data that is displayed on the report, you can use the Blank Report tool to build a report from scratch. This can be a very quick way to build a report, especially if you plan to display only a few fields. The following procedure explains how to use the Blank Report tool:

On the Create tab, in the Reports group, click Blank Report.
-OR-

A blank report is displayed in Layout view, and the Field List task pane is displayed on the right side of the Access window.
In the Field List task pane, if no tables are displayed, click Show all tables.
Click the plus sign (+) next to the table or tables containing the fields that you want to see on the report.
Drag each field onto the report one at a time, or hold down CTRL and select several fields, and then drag them onto the report at the same time.
Use the tools in the Header / Footer group on the Design tab to add a logo, title, page numbers, or the date and time to the report.

Top of Page

Create a report: non-web-compatible report tools

You can use the tools described in this section to create reports for both client and web databases; however, the resulting reports cannot be viewed in a browser. In other words, you must open the database in Access before you can view a report that you create by using one of these tools. If you are working in a web database, these report tools are listed under Client Reports on the Create tab.

Create a report in Report Design view

In Design view, you can more easily see which controls are in which section of the report. You can position controls individually without using layouts,

On the Create tab, in the Reports group, click Report Design (in a web database, click Client Reports, and then click Report Design).
In the Field List task pane, if no tables are displayed, click Show all tables.
Click the plus sign (+) next to the table or tables containing the fields that you want to see on the report.
Drag each field onto the report one at a time, or hold down CTRL and select several fields, and then drag them onto the report at the same time.
Use the tools in the Header / Footer group on the Design tab to add a logo, title, page numbers, or the date and time to the report.
Add controls to layouts, or remove them from layouts, by using the commands on the Arrange tab, in the Table group.

Create a report by using the Report Wizard

You can use the Report Wizard to be more selective about what fields appear on your report. You can also specify how the data is grouped and sorted, and you can use fields from more than one table or query, provided you have specified the relationships between the tables and queries beforehand. Find links to more information about creating relationships in the See Also section.

On the Create tab, in the Reports group, click Report Wizard(in a web database, click Client Reports, and then click Report Wizard).
Follow the directions on the Report Wizard pages. On the last page, click Finish.

When you preview the report, you see the report as it will appear in print. You can also increase the magnification to zoom in on details.

Note: If you want to include fields from multiple tables and queries in your report, do not click Next or Finish after you select the fields from the first table or query on the first page of the Report Wizard. Instead, repeat the steps to select a table or query, and click any additional fields that you want to include in the report. Then, click Next or Finish to continue.

Create labels by using the Label Wizard

Use the Label Wizard to easily create labels for a wide variety of standard label sizes.

In the Navigation Pane, select the table or query that will serve as the record source for your labels.
On the Create tab, in the Reports group, click Labels.
Follow the directions on the pages of the Label Wizard. On the last page, click Finish.

Access displays your labels in Print Preview so that you can see them as they will appear when they are printed. You can use the slider control on the Access status bar to zoom in on details.

Note: Print Preview is the only view you can use to see multiple columns — the other views show the data in a single column.

Top of Page

Understand the report sections

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. 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.
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.
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, 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

Understand 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:

Bound control A control whose source of data is a field in a table or query is a bound control. 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 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 (a field or expression) is an unbound control. 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 is a calculated control. 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 (0.75).

= [Unit Price] * 0.75

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

Find links to more information about expressions in the See Also section.

When you create a report, it is 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 on the Design tab.

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, on the Design tab, in the Controls group, click Add Existing Field.

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.

Using the Field List pane is the best way to create a 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 created 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

Add controls to the report

Some controls are created automatically, such as the bound text box control that is created when you add a field from the Field List pane to your report. Many other controls can be created in Layout view or Design view by using the tools in the Controls group on the Design tab.

Note: You can determine the name of each tool by placing the mouse pointer over the tool and then reading the tooltip that appears.

Create a control by using the tools in the Controls group

Click the tool for the type of control that you want to add.
Click on the report where you want the control to be located.
If you don't position the control perfectly on the first try, you can move it by using the following procedure:
1. Click the control to select it.
2. Position the mouse pointer over the edge of the control until the pointer turns into a four-headed arrow .
3. Drag the control to the location that you want.

This procedure creates an "unbound" control. If the control is the type that can display data (a text box or check box, for example), you need to enter a field name or expression in the ControlSource property for the control before it will display any data. See the Understand controls section in this topic for more information.

Display the property sheet

To display the property sheet in Design view, do one of the following:

On the Design tab, in the Tools group, click Property Sheet.

-OR-

Press F4.

Top of Page

Microsoft Office Tutorials

Sunday, December 25, 2016