MS Access 2003 - Lesson 31: Creating a Report

Note : This section uses the My Friends database you created in Chapters 2 and 3.

To get started with Report Wizard, make sure the Reports tab is selected in the Database window. Then, double click on the Create Report By Using Wizard . Access displays the Report Wizard dialog box as shown in Figure 1.

Figure 1: Report Wizard dialog box

On the left side of the Report Wizard dialog box, you can use the drop-down list to determine which sources Access should use for this new report. Remember that you can use both source data as tables and queries. Because the My Friends database has only one table (My Friends table) and Access has chosen it for you. If you are working with a database that already contains other tables and queries, you can use the drop-down list to change the data source.

Select information

At the bottom left of the dialog box is a list of all the fields specified in the table or query selection. If you change the data source from the list, the existing fields will also change. You can use the list and controls on the right side of the list, to indicate which fields you want to include in the report you are creating. From a simple report, you use the following fields:

1. Last Name
2. First Name
3. Address
4. City
5. State
6. Zip Code
7. Home Phone
8. Work Phone

To let Access know you want to use a field in the report, click on the field name (in the order shown below) and click your mouse on the > button. Access will move the field from the left column to the right column. Repeat this process until you select and move all the fields listed above. Once done, click your mouse on the Next button. Access displays the dialog box shown in Figure 2.

Figure 2: Select the fields to group.

Group of information

The next step is to decide if you want the information grouped in any particular order. Grouping is often done in financial reports, although it does not mean limiting the financial statements. For example, if you create a report that displays sales for a company, you want the information in the report to be grouped by month, by salesperson or by region.

Creating a simple report based on the My Friends table, grouping may be unnecessary. To see how it works, say that you have a large number of friends in the database and want to be grouped by the State field. To do so, simply select the State field on the left side of the dialog box and click on the > button. The Report Wizard dialog box changes to display grouping as shown in Figure 3.

Figure 3: Grouping will help you organize your output in a report.

Note that the State field is moved to the top of the report template and the Group Options button is active. With some types of reports, you can change group options. Doing so will allow you to determine how Access should analyze the field you are grouping. In this case, changing the grouping option is not necessary.

To proceed to the next step, click your mouse on the Next button. Access displays the dialog box shown in Figure 4.

Figure 4: Select the fields to sort by.

Arrange information for the report

Now, you need to decide how the information in the report is arranged. Access allows you to specify up to 4 sortable fields. In most cases you only use one or 2 schools to arrange. If you create a list of friends, and want their names sorted alphabetically. You should select the LastName and FirstName fields.

Use the first drop-down list with sorting to select the LastName field. If you want, you can click the sort button to the right of the field to indicate that Access should sort the field in ascending or descending order. And now, you use the second drop-down list to select the FirstName field.

Once you have selected the fields to sort, you can click your mouse on the Next button to proceed to the next step. Access displays the dialog box shown in Figure 5.

Figure 5: Determine the layout for the report.

Define Report Layout

Using the dialog box shown in Figure 5, you can indicate different options for the layout of the report. Optional buttons in the dialog box adjust the different layouts. The best way to find out how you want data to appear is to try different buttons. Access changes the report template to indicate information.

Note that the modifications in the dialog box do not work with the content, but only in the way reports present the content you have chosen. Besides selecting a layout style, the dialog box also allows you to select an orientation for the report (that is, horizontal or vertical rotation of the report). According to the fields included in this report, you definitely choose horizontal rotation.

You can also use this dialog to indicate that Access should limit the size of each field at the output. If the Adjust check box (below the dialog box) is selected, Access may cut off some data. If you omit this check box, the data is not cut off but it may not lie on one line. For the purposes of this report, accept the default to allow the width of the column to be adjusted. (Remember that you can always change the report layout later).

When done, click your mouse on the Next button to proceed to the next dialog box (Figure 6).

Figure 6: Select report type.

Here, you have the opportunity to determine how you want the content to appear. Access provides 6 predefined styles that define report elements such as font, color, line, etc. You can select each type in turn and see how your report will appear in the box. phone. Select a style and click the Next button. Access displays the final dialog box shown in Figure 7.

Figure 7: Finish creating report

You have nearly completed the report definition. In the last dialog box, Access asks you about the title used for the report. This title is used in two locations: at the top of each page in the printed report, and when saving the actual report in the database. Note that the Wizard provides the same name as the table used. In this case it is a suitable title, so you can reuse it.

You are now ready to click your mouse on the Finish button. Then, Access uses the print preview property to show you how the report will be printed. Here's how you can see if you need to make any changes. Figure 8 shows the final report.

Figure 8: Final report

Report Wizard has created a great report. There are some changes you can make (such as changing margins, field widths), but changing the entire report is not recommended. In chapter 14, you will learn how to use the report design window and so you can create separate reports from the mix and make changes to the report.

When you have finished viewing the report, close the print preview window by clicking your mouse on the Close tool on the toolbar or by clicking the Close icon (X) in the upper right corner of the window. Access only displays the Design window when finished. You can close the design window by clicking your mouse on the Close icon in the upper right corner of the Design window. You have now completed the first report in Access.