5 Reports

Creating and Running a Report

Reports are similar to queries in that they retrieve data from one or more tables and display the records. Unlike queries, however, reports add formatting to the output including fonts, colors, backgrounds and other features. Reports are often printed out on paper rather than just viewed on the screen. In this section, we cover how to create simple reports using the Report wizard.

5.1 Creating a Single Table Report using the Wizard

In this example, we will create a simple report for a single table using the Report wizard. First, select the Reports tab from the Access main screen.

To create a new report, double-click 'Create Report by Using Wizard'.

In the first step of the Report wizard, we need to specify the fields from the Customer table that will appear on the report. In this case, we want all of the fields to appear. Move each of the fields from the Available Fields side over to the Selected Fields side as in the following figure. Then click on the Next button.

In the next step, we have the opportunity to add Grouping Levels to the report. A grouping level is where several records have the same value for a given field and we only display the value for the first records. In this case, we will not use any grouping levels so simply click on the Next button as shown below.

In the next step, we are given the opportunity to specify the sorting order of the report. For this example, we will sort the records on the CustomerID field. To achieve this, pull down the list box next to the number 1: and choose the CustomerID field as shown in the figure below. Then click on the Next button.

The next step is to specify the layout of the report. The three options are:

	Columnar - Places the labels to the left of each field. This is similar to a paper form.
	Tabular - Places the field labels at the top of the report page and the records are displayed below. This is similar to how a spreadsheet would display the data.
	Justified - Places the labels above each field with the fields spread out on the report page.

Generally, reports use the tabular layout. For this example, choose Tabular layout and set the page Orientation to Landscape so that all of the fields will fit across one page. This is shown in the figure below. Click on the Next button to continue.

In the next step, the style of the report can be selected. For this example, choose the Corporate style and click on the Next button to continue.

Finally, give a name for the new report: CustomerReport and then click on the Finish button to create, save and display the new report. The output from the report is shown in the figure below.

Once the report is displayed, it can be viewed, printed or transferred into Microsoft Word or Microsoft Excel. The button bar across the top of the screen has the following functions:

	Print the report
	Zoom into a region of the report
	Display the report as one, two or multiple pages
	Zoom into or out of the report
	Transfer the report into MS Word
	Close the report

To close the report and return to the Access main screen, pull down the File menu and choose Close or click on the Close button.

5.2 Exercise: Creating a Single Table Report

For this exercise, we will create a report showing all of the Accounts information.

From the Reports tab on the Access main screen, click on the New button.
Select the Report wizard, select the Accounts table and then click the OK button.
Select all of the fields in the Accounts table by moving them all over to the Selected Fields side and then click Next
You will probably find that the report is already grouped by CustomerID as shown in the following figure. If the report is not already grouped by CustomerID then group it by clicking on the CustomerID field and then clicking on the right arrow button.

Click on the Next button.
Choose to sort the report on the AccountNumber field.

Click on the Summary Options button. Choose the Balance field and select the Sum option. Choose the option to show both Detail and Summary data. Then click on the OK button.

Click on the Next button.
Choose a Block layout and click on the Next button.
Choose the Corporate style and the click on the Next button.
Finally, name the report: AccountsReport and click on the Finish button to create, save and run the report.

The output from the AccountsReport is shown below:

Note the Grouping at the level of the CustomerID and the Sum for each customer's balances.

Close the report and return to the Access main screen.

5.3 Review of Creating and Running a Report

As can be seen in the report exercise, there are many ways to create reports to show summarization, sorting and layout of the data. Further study of Reports will show how to modify the layout using the Design View. Students are encouraged to work with the Report wizards to create different styles and types of reports.