Applies to
Microsoft Office Access 2003 Microsoft Access 2000 and 2002

You can create a variety of different reports in Access, ranging from the simple to the complex. Certain principles apply, regardless of the report type. For example, you start by thinking about your report's record source. This holds true whether the report is a simple listing of records or a grouped summary of sales by region. Either way, the record source provides the data for your report, in the form of information pulled from either a table or a query.

After you choose your record source, you'll usually find it's easiest to create your report by using a report wizard. A report wizard is a feature in Access that guides you through a series of questions and then generates a report based on your answers. It's the best way to jump-start creating a report.

In this article, we explore the steps in creating a simple report.

Contents

Choose a record source
Create the basic report by using a report wizard
Understand the report sections
Fine-tune your report in Design view
Add fields to the report
Add controls to the report
Save your work
Preview your report
Print your report
Send your report as an e-mail message

Choose a record source

A report consists of information that is pulled from a table or query, as well as information that is 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 that you want to include. 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 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.

For more information about queries, see About designing a query (MDB).

Create the basic report by using a report wizard

The AutoReport Wizard provides the fastest way for you to jump-start creating a report, because it generates a report immediately, without prompting you for information. The report is a preformatted single-column report that displays all the fields in the underlying table or query. The AutoReport Wizard 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, or as a starting point that you can fine-tune in Design view.

To create a report by using the AutoReport Wizard

1. In the Database window, click Tables or Queries.
2. Select the table or query on which you want to base the report.
3. Click the New Object list on the toolbar.
4. Click AutoReport.

   Access displays the report.

- OR -

1. In the Database window, click Reports.
2. Click New on the Database window toolbar.
3. In the New Report dialog box, click one of the following:

   AutoReport: Columnar  Each field appears on a separate line with a label to its left.

   AutoReport: Tabular  The fields in each record appear on one line, and the labels are printed once at the top of each page.

4. Click a table or query for your report.
5. Click OK.

   Access applies the last autoformat (autoformat: A collection of formats that determines the appearance of the controls and sections in a form or report.) that you used to format the report. If you haven't created a report with a wizard before or haven't used the AutoFormat command on the Format menu, Access uses the standard autoformat.

To create a report with another report wizard

1. In the Database window, click Reports.
2. In the Database window, click New.
3. Access displays the New Report dialog box.
4. Select one of the choices in the list.

   

5. Select a table or query for the report, and then click OK.
6. Follow the directions on the Report Wizard pages. On the last page, click Finish.

   

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

Notes

• If you want to include fields from multiple tables and queries in your report, don't click Next or Finish after you select the fields from the first table or query in the Report Wizard. Instead, repeat the steps to select a table or query, and click fields that you want to include in the report, until you have selected all the fields you want.

• If you click one of the AutoReport options, Access uses the autoformat that you last specified, either in the Report Wizard or by using the AutoFormat command on the Format menu in Design view.

 Back to top

Understand 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. Here's a summary of the section types and their uses:

• Report Header  This is 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. 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 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 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 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 is printed at the end of each group of records. Use a group footer to print summary information for a group.
• Page Footer  This is printed at the end of every page. Use a page footer to print page numbers or per-page information.
• Report Footer  This is 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 that the report footer appears last in the report design but is printed before the final page footer.

Fine-tune your report in Design view

After you create a basic report, you can fine-tune its design by working in Design view. You can use the toolbox (toolbox: A set of tools that is available in Design view to add controls to a form, report, or data access page. The toolset available in page Design view is different from the toolset available in form and report Design view.) to place controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) on your report design, or the field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) to place fields (field: An element of a table that contains a specific item of information, such as a last name. A Title field might contain Mr. or Ms. Databases such as Microsoft SQL Server refer to fields as columns.). The property sheet (property sheet: A window that is used to view or modify the properties of various objects such as tables, queries, fields, forms, reports, data access pages, and controls.) gives you access to a large number of properties (property: A named attribute of a control, a field, or an object that you set to define one of the object's characteristics (such as size, color, or screen location) or an aspect of its behavior (such as whether the object is hidden).) that you can set to customize your report.

To switch to Design view:

• On the View menu, click Design view (or click the arrow next to the View button on the toolbar, and then click Design View).

Access shows the Report window in Design view.

You use the Property sheet to modify the properties for the report and its controls and sections.

The toolbox displays all of the tools that you can use to add controls to your report design.

The design of the report is divided into Report sections.

You use the Field list to add fields from the underlying table or query to your report design.

You use the toolbox to place controls on your report design. 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 (bound control: A control used on a form, report, or data access page to display or modify data from a table, query, or SQL statement. The control's ControlSource property stores the field name to which the control is bound.). 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 (a field or expression) is an unbound control (unbound control: A control that is not connected to a field in an underlying table, query, or SQL statement. An unbound control is often used to display informational text or decorative pictures.). 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 (calculated control: A control that is used on a form, report, or data access page to display the result of an expression. The result is recalculated each time there is a change in any of the values on which the expression is based.). 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.

  For more information about expressions, see the topic Create an expression.

A text box can be a bound control, an unbound control, or a calculated control. When you create a report that uses bound, unbound, and calculated controls, 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 toolbox.

You bind a text box 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 to the report. (The field list displays the fields of the report's underlying table or query. To display the field list, on the View menu, click Field List.) When you drag a field from the field list, you create a text box by default.

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 is the best way to create a bound text box for two reasons:

• A bound text box 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 text box 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 text box 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. For details about the ControlSource property, search Help for "ControlSource."

Add fields to the report

To add a field to the report, first make sure that the field list is displayed. The field list contains a list of all of the fields in your record source.

To display the field list

• On the View menu, click Field List (or click the Field List button on the toolbar).

To add fields from the field list

• To add a single field, drag the field from the field list to the section where you want it displayed on the report.
• To add several adjacent fields at once, click the first field that you want, and then hold down SHIFT and click the last field that you want. Drag the selected fields onto the report.
• To add several nonadjacent 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.

Add controls to the report

All of the information on a report is contained in controls. A control is an object that displays information, performs an action, or decorates your report. Some controls are bound to fields in the underlying table or query, so you can either enter data into the fields or display data from them. For example, you can enter and display information by using a text box control. Other controls display information that is stored only in the report design and is not connected to a data source. For example, you use labels to display descriptive text, and you use lines and rectangles to make a report more attractive.

Some controls are created automatically, such as the bound text box control that is created when you add a field from the field list to your report. Other controls are created by using the toolbox.

To display or close the toolbox  

• On the View menu, click Toolbox (or click the Toolbox button on the toolbar).

To determine the name of a tool  

• Place the mouse pointer over the tool.

  Access displays the name of the tool.

To create a control by using the toolbox  

• 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.
• Create a bound control by selecting a field in the field list and then dragging it onto the report.

  —or—

  Create a calculated or unbound control by clicking the report where you want to position the upper-left corner of the control. Click once in the report to create a default-sized control, or click and drag in the report to create a control of the size that you want.

For more information about controls, see About types of controls in Access.

 Back to top

Save your work

After you save your report design, you can use it over and over again. The report's design stays the same, but you get current data every time you print the report. If your reporting needs should change, you can modify the report design or create a new, similar report based on the original.

To save your report design  

1. On the File menu, click Save (or click Save on the toolbar).
2. If the report is untitled, type a name in the Report Name box, and then click OK.

To save your report design under a new name  

1. On the File menu, click Save As.
2. In the Save As dialog box, type a name in the Save Report to box, select Report in the As box, and then click OK.

Preview your report

When you preview a report, you see how it will look when it is printed without actually printing it. For example, you can check the alignment and spacing of columns of data, or see if the report is returning the data that you want. When you preview a report, you can choose from two views:

• Layout Preview  Use Layout Preview to check the font, font size, and general layout. Layout Preview displays all the sections of a report and a few detail records.
• Print Preview  Use Print Preview to see the entire report exactly as it will be printed.

When you display a report in Layout Preview or Print Preview, you see a close-up view of it.

To preview a sample of a report from Design view

• On the View menu, click Layout Preview (or click the arrow next to the View button on the toolbar and then click Layout Preview).

  To return to Design view, click Close on the toolbar.

To preview an entire report from Design view

• On the View menu, click Print Preview (or click the arrow next to the View button on the toolbar and then click Print Preview). To return to Design view, click Close on the toolbar.

To preview a report from the Database window

1. In the Database window, click Report and then select the report that you want to preview.
2. In the Database window, click Preview or click Print Preview on the File menu.
3. To return to the Database window, click Close on the toolbar.

To navigate to another page in Print Preview

You can use the navigation buttons to view the pages of a report sequentially or jump to any page in the report.

1. Open the report in Print Preview.
2. Click one of the navigation buttons (lower-left corner of the window), or type the page number that you want to see in the page number box, and then press ENTER.
3. To return to the Database window, click Close on the toolbar.

First page navigation button

Previous page navigation button

Next page navigation button

Last page navigation button

To view a specific page, type a page number in this box.

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.

Tip  After previewing the report, you can publish the results to Microsoft Office Word 2003 or Microsoft Office Excel 2003 by clicking the arrow next to the Officelinks button on the toolbar and then clicking either Publish It with Microsoft Office Word or Analyze It with Microsoft Office Excel. Additionally, you can send the output as an e-mail message by pointing to Send To on the File menu and then clicking Mail Recipient (as Attachment).

Print your report

You can print a report from Print Preview, Layout Preview, Design view, or from the Database window. Before printing, you'll want to double-check page settings, such as margins or page orientation. Access saves the page settings with the report, so you need to set them only once, and again later, only if your needs change.

Change the page settings

1. Open the report in Design view or Print Preview (or select the report in the Database window).
2. On the File menu, click Page Setup. (If you are in Print Preview, you can click Setup on the toolbar.)

   Access displays the Page Setup dialog box.

3. On the Page tab, under Orientation, click an option:
   • Portrait orients the page vertically.
   • Landscape orients the page horizontally.

     You can also change the paper or printer settings from this dialog box.

4. Click OK.

To print a report directly from Print Preview, click Print on the toolbar, or on the File menu, click Print.

Send a report to a printer

1. Open the report in any view (or select the report in the Database window).
2. On the File menu, click Print. (If you are in the Database window, in Layout Preview, or in Print Preview, you can click Print on the toolbar.)

   Access displays the Print dialog box.

3. Enter your choices for options such as printer, print range, and number of copies.
4. Click OK.

Send your report as an e-mail message

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

To send a report as an e-mail message  

1. Switch to Print Preview or Design view.
2. On the File menu, click Send To, and then click Mail Recipient (As Attachment).
3. In the Send dialog box, in the Select Format list, click the file format that you want to use.

   

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

 Back to top