Reports provide ways to retrieve and present selected information stored in your database. You can base a report on a table or query. Reports are designed to be printed rather than viewed only on a computer screen, so they need to be carefully planned to make sure that you use them most effectively and meaningfully. Like queries, reports do not store the data they present. Examples of reports that we use every day are mailing labels, invoices, receipts, sales summaries, and phone books. Since you will be using reports to make presentation quality documents, they need to possess enough design flexibility to allow you to get the right message across to your audience. In Access you can combine text, data, pictures, lines, boxes, graphs, and drawings to design exactly the report you need. You can save the design of a report as a kind of template that you can use over again. Fortunately, Access also comes with several pre-planned report layouts that you can set up in a few minutes using Report Wizards.

Access reports are especially useful if you need to:

In the database window, you have two options through which to open a report: Print Preview and Design View. These are also viewing options when you are inside a report. Preview view allows you to see the report as it will look on the printed page, that is, with actual data culled from the underlying table or query. You can only use this view if you have a printer listed as your default printer for Windows. You cannot make changes to a report in Preview view. Instead, you make changes in Design view. As with forms and tables, Design View, allows you to see how your report is put together and to change its formatting. It gives you access to a full toolbar, the toolbox, rulers, grid, the Properties window, and the status bar.

You can move between these two views very easily. In Design View, select the Print Preview icon from the File or View menu (shown above) or click on the same Print Preview icon on the toolbar. From the Print Preview screen, select the Design View icon from the View menu (also shown above) or click the Design View icon on the toolbar in order to get back to Design view.

Another view for reports is Layout Preview, which is a modified version of Print Preview. This mode is ideal if you just need to do a quick check for layout features. You should use Print Preview to check the data for the entire report. The icon for Layout Preview is also located on the toolbar as well as in the View menu.

Like all Access objects, you create a new report by selecting the New button in the Database Explorer. The New Report window appears and you are then presented with two options: using one of several wizards or designing your own report from scratch by selecting Design View.

Note that you can also create a report using Wizards by selecting Create report by using wizard in the Access window. You can also create a report from scratch by selecting Create report in Design view in the Access window.

One advantage of wizards is that they perform many basic formatting tasks and allow you to concentrate on refining special features of the report. There are four basic types of Report Wizards:

To create a record from scratch, click the Reports tab in the Access Window, and then select Create report in Design view, or select Design View in the New Report window. What appears is a blank report in Design View upon which controls will be placed.

Understanding the structure of a report is very important when you design your own reports. Access reports use three nested sections, which you can see by examining a report in Design View (shown above). The three basic sections of a report are:

The Details section is always nested between the page headers and footers, and the page headers and footers are always nested between the report headers and footers. You can create or delete page headers and footers as well as report headers and footers in Design view by right-clicking the screen and checking (or unchecking) the appropriate items in the menu that appears. You can also check (or uncheck) Report Header/Footer or Page Header/Footer in the View menu.

As with any published document, too much blank paper and not enough information can ruin a good report. Generally, most report sections should be only slightly larger than the information displayed in them. This is why it is important to know how to change the size of sections. To do this, place the mouse pointer on the bottom or right edge of the section you want to adjust. The pointer will change from the standard white arrow to a black, multi-pointed arrow when you are on the edge. Then, drag the edge until the section is the size you want. You can use the rulers at the top and left of the form to size the sections exactly. You can change the height of sections independently, but a report has only one width. When you change the width of a section, you change the width of the entire report.

Reports and forms use the same controls. Since controls are introduced in the ITS site [ http://help.unc.edu/?id=212 ] Microsoft Access XP/2000 Users: Controls, we will briefly review the controls that are most useful for reports. There are three basic types of controls: bound, unbound, and calculated. Bound controls are those whose source of data is a field in a table or query in your database. A text box is the most common type of bound control. As the user navigates through the records on which a report is based, bound controls will display the values in the fields of each record. An unbound control does not have a data source in your database. Unbound controls include labels, lines, rectangles, and pictures and contain values that the database developer provides. These values do not change as the user navigates through the records. A calculated control is based indirectly on an underlying table or query in that its data source is an expression that is performed on the underlying data. This expression is usually written in the control itself. An example of a calculated control would be an arithmetic operation carried out on a given field or fields, such as:

=[Num_FT_Students] + [Num_PT_Students]

In this case, the values in two fields in an underlying table or query, Num_FT_Students (which tallies the number of full time students) and Num_PT_Students (which tallies the number of part time students), are added in the calculated control.

The controls most often used in Access are:

One important reminder: Since reports are designed for displaying and print information, users cannot interact with controls in reports like they can in forms. This means that users cannot enter or change values presented by controls in a report.

For more information on sizing, moving, aligning, or assigning properties to controls, go to the ITS site [ http://help.unc.edu/?id=212 ] Microsoft Access XP/2000 Users: Controls.

Using page breaks is a good way to break information into discrete units. If you want to divide the information within a report section between two or more pages, you use a page break control to mark where you want the new page to begin. Below is the page break icon as it appears on the toolbox.

In Design view, the page break control appears as a dotted line. In Print Preview, however, everything above the control in the section appears on one page and everything below it in the section appears on the following page.

If you want to force a page break after or before a particular section of your report, go to Design view and select the section by clicking on the bar above it. The bar will become black to indicate that the section is selected. Then click the field next to the Force New Page property under the Format tab in the Properties window. A drop down menu then appears and you can select where the new page will appear.

Like many word processors, Access's report formatting options allow you to create a template to use for other reports. When you create any new report from scratch (that is, without the Report Wizard) either by pressing Design View in the New Report window or Create report in Design view in the Access window, Access uses a default template to determine the report's initial characteristics, namely, which sections the report will contain and where on the page they will appear. The default Access report template is called Normal. If you design a report that you want to use as a template for other reports inside the same database, you can change the default template name from Normal to the name of the report you want to use. You can also create a blank report to use just as a template.

Creating a Report Template:

At this point, nothing will be changed. However, the next time you create a report from scratch, the new report will use the template you just saved. This means that all new reports will contain sections proportioned exactly how the sections were proportioned in the original report. This includes only the report header and footer, the page header and footer, and the details section. New sections created by you in the original report will not appear in the new one.

Note that a report based on a template will still be empty. Creating a template does not include the controls that were on the original report. Note also that Access does not default back to Normal, even if you close the application. This, or the entry of any new template, must be done manually.

When creating a report, you will often need to arrange the data in a particular order. For example, if you are printing out a mailing list, you might want to sort the names alphabetically or to sort them by increasing Zip Code. You use the Sorting and Grouping window to establish the sort order. You open this window by selecting Sorting and Grouping in the View menu or by selecting the Sorting and Grouping icon from the toolbar (shown below).

You use the top part of the window to set the sort order for the records in a report. You can sort up to ten fields and expressions, and you can sort on the same field or expression more than once. Field/Expression specifies the name of the field or expression on which to sort. The order of sorting for the report goes down the column. Sort Order tells Access how to sort the data in the report. You can choose to sort in either ascending or descending order. In Access, there is no easy way to specify sort orders other than these two kinds.

The other part of the Sorting and Grouping window is Group Properties. This feature allows you to divide data into groups. A group consists of a collection of records that share a common piece of data, along with any introductory and summary information displayed along with the records. A group consists of a group header, nested groups (if you have any), detail records, and an optional group footer. Grouping gives you a formatting advantage by allowing you to separate groups of records visually, and display introductory and summary information for each group. You might, for example, want to group a company's sales by date and then calculate the total sales for each day. You can group on any fields and expressions you sort on, up to 10. When you group on more than one field or expression, Access nests the groups according to their position in the sort order. The first field that you are sorting on should be the first and most significant group level. The second field you group on should be the next grouping level, and so on. This is, in fact, how you create new sections in Access.

Group Headers and Footers

When you specify sort fields in the Sorting and Grouping window, you actually create groups by adding group headers and footers. After selecting the field that you want to group on, you can set the Group Header and Group Footer properties in the Group Properties part of the box. When you add a header or footer by choosing "Yes" in either of the property boxes, Access places a grouping icon next to the field or expression. You then complete the group by adding the controls you want to appear in the group header or footer in the Design view.

Setting Grouping Ranges and Intervals

To specify the value or range of values that begin and end a particular group, you use the Group On and Group Interval properties in the Sorting and Grouping window. These properties work hand in hand. First, you select the group field in the Field/Expression part of the window, then you can set the Sort On and Group Interval properties.

When you group records on a text field from an underlying table or query, you have two options for setting the Group On property: Each Value and Prefix Characters. Each Value allows you to group records containing the same value in the field or expression, Prefix Characters allows you to group on records with the same first n characters.

When you group records on a date field from an underlying table or query, you have many options for the Group On property: Each Value and Year, Qtr, Month, Day, Hour, and Minute. Each Value is the same as above, and the date values allow you to group on records according to certain dates or times.

When you group records on an autonumber, currency, or number field from an underlying table or query, you again have two options for the Group On property: Each Value and Interval. Each Value is the same as above, and the Interval value allows you to group values on intervals you specify.

You use expressions to get information that you cannot get directly from the tables in a database. Most calculated controls will use an expression. An expression might calculate and display the total sales of an employee for one month. The result of an expression is not stored, but is calculated each time the report is printed or previewed. People often use text boxes to calculate values, but almost any control that has a Control Source property can be used. Two common expressions used in reports calculate the current date and page numbers.

Printing the Current Date:

There are two Access functions that allow you to calculate the current date: Now and Date. Now gives the current date and time as it is stored in your computer (many computers can lose as much as three minutes each day!). Date() yields only the current date. You can format the result of either of these functions by changing the control's Format property (found in the properties window under the Format tab). To add either of these functions, select the control that you want to contain the date, then enter =Now or =Date() into the control itself or in the control's Control Source property (under the Data tab in the Properties window). The current date will appear in the control whenever the report is printed or viewed on the Print Preview screen.

Adding Page Numbers:

When you print a report that is longer than one page, you will probably want to add page numbers. You add page numbers by using the Page function, which automatically numbers the pages when you print or preview the report. You use the Page function in a text box by entering =Page into the control itself or in the control's Control Source property. You can also calculate the total number of pages in the report with the Pages function. These functions can be used together. For example, if you enter ="Page " & [Page] & " of " & [Pages], then you will end up with "Page 1 of 10."

Calculating Totals on a Report:

Access gives you the ability to calculate totals easily for both individual records, groups of records, and entire datasets. For instance, you could create expressions to calculate both the hours worked by one employee and the hours worked by an entire department. The most common functions used in calculating totals are Sum ( expr ) and Count ( expr ). The expr argument is the name of a field or an expression. You can use field names in the argument for an expression, but not control names. The field name must come from a table or query, including calculated fields in a query. When you calculate totals, the section of the report where you place the control expression determines the total. To calculate a total for a single record, place the calculated text box in the detail section. To calculate a total for a group of records, place the calculated text box in the group header or footer. To calculate a total for an entire dataset, place the calculated text box in the report header or footer. To calculate the number or hours that a given employee worked (based on the field Hours_Worked in the underlying table or query), you would enter =Sum([Hours_Worked]) in a textbox within the detail section. If you then grouped employees by department, you would enter =Sum([Hours_Worked]) in the group footer.