What are Reports?
A report provides a way to retrieve and present selected information stored in your database. You can base a report on either a table or a query. Reports are designed to be printed out rather than viewed on a computer screen, so they need to be carefully planned to make sure that you use them most effectively and meaningfully. 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. Fortunately, Access also comes with several pre-planned report layouts that you can set up in a few minutes using Report Wizards .
Why Use Reports?
Access gives you several methods of retrieving information from your database and displaying it. You can use a table, a query, a form, or a report. To look at all of the entries in your database using a spreadsheet-like view, use a table. To see only a portion of your data in spreadsheet-like view, use a query. To see your data one record at a time, use a form. To group your data and present it in an attractive format, use a report. In most situations, none of these descriptions will describe exactly what you are trying to do, so you should generally assume that reports will be used anytime you need to print carefully formatted information.Like queries, reports do not store the data they present. They keep only the design features that you specify. This allows you to save the design as a kind of template that you can use over again. Once you save the report design, it stays the same, but you will retrieve current data from the table or query each time you print the report. Access reports are especially useful if you need to:
- Organize and present data in groups
- Calculate running totals, group totals, grand totals, and percentages of totals
- Include subreports and graphs
- Present your data in an attractive format with pictures, lines, and special fonts. This option is easy if you use Access's pre-designed templates, but can be difficult to do from scratch
Views in Reports
In the database window, you have two options to open a report: Preview and Design. These options are also your viewing options when you are inside a report. Preview view allows you to see the report as it will look on the printed page. You can only use this view if you have a printer listed as your default printer for Windows. As in tables and queries, 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, and the status bar. In Design view, you can move between these two views by going to the Report Design toolbar and clicking on the Print Preview button (or choose Print Preview from the File menu). From the Print Preview screen, you need to choose the Close button on the Print Preview toolbar in order to get back to the Design view. Another view for reports is a modified version of Print Preview ; Layout Preview mode is useful if you just need to do a quick check for layout features, but you should use Print Preview to check the data for the entire report.
Creating a New Report
Like all Access objects, you create a new report by selecting the New button in the Database Explorer . You are then presented with two options: using wizards or designing your own report from scratch. If you choose to use wizards, you will be prompted to enter information to help Access determine how to format your report. You can also use the AutoReport Wizards , which create a columnar or tabular report automatically without prompting you for any information. This option can be helpful, but always defaults to a report displaying all the fields in the underlying table or query. In all cases, a report always needs to be based on an underlying table or query. If you choose to start with a blank report, then you will have to format everything on your report.
These wizards, more than any others in Access, are the most useful starting point for their task. Report wizards include four standard report formats: Columnar, Tabular, Mailing Labels , and Charts. They take care of some of the complex preliminary tasks you must carry out to design an effective report. The report wizards let you choose from several report styles, but you may find it more helpful to design your own templates. These styles control layout, font, positioning, and colors. They demonstrate effective presentation techniques and eliminate some of the grunt work needed to produce a nice looking report.
Anatomy of a Report
Before you edit a report created with wizards or create one of your own from scratch, you need to think about how reports are put together. The quickest way to understand how a report works is to examine a printed report and figure out what each part does. In the report shown here, the report titles, captions, and expressions are stored in the report design. The information retrieved (the Order #, Company, and Sale Amounts) comes from the fields in an underlying table or query.
Nested Headers and Footers
Understanding the structure of a report's design is very important when you design your own reports. Access reports use three nested levels of headers and footers, which you can see by examining the headers and footers in the Design View (shown in the example). The Report Header and Report Footer come at the beginning and end of the report respectively. Page Headers and Page Footers are the next level of information inside of the headers and footers. These headers and footers allow you to control the information that appears on every page. Often, the report headers and footers contain labels that describe the overall report, while page headers and footers provide information on each page. Individual section headers and footers are nested inside of the page headers and footers. These headers and footers frequently contain fields with data common to several records in the report, such as a date or the expression that totals the data presented in that section. The lowest nesting level in an Access report is a Detail. Details usually contain the controls linked to individual fields that are being presented in the report.
Reviewing Controls
Reports and forms use the same controls. Since controls are introduced in the Access 97: Forms handout, 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. An unbound control does not have a data source in your database. Unbound controls include labels, lines, rectangles, and pictures. A calculated control's data source is an expression rather than a field. An example of a calculated control would be an arithmetic operation carried out on a given field. You could create a control that would count the number of students enrolled in a class with:= Count([Student Last Name]).
Selecting, Moving, and Sizing Controls
One of Access's better features is the easy drag and drop movement of the controls on forms and reports. You select a control by clicking on the control. You can also select several controls by lassoing them. To lasso, you need to click in open space on the report, then drag the pointer through all of the controls that you want to select. Once selected, you can move a control and its attached label anywhere in the report. If the cursor is placed on a control's border, you will see one of three options. An open hand means that you can move both the control and its attached label. A hand pointing with the index finger means that you can move only the selected control or label. A double-headed arrow allows you to re-size the control. You move or re-size the control by holding down the left mouse button and dragging it. However, if you want to move only a control or only its attached label, you have to cut and paste the desired part.
Using Yes/No Values in Reports
Check boxes and option buttons are symbolic controls that display information in your database that has been entered in a Yes/No or True/False format. When selected these controls indicate a Yes or True value. When cleared, they represent a No or False value. A check box is checked when selected, and an option button has a dot when selected. The only difference between these types of control is their appearance, so you can select whichever looks best on the report you are designing.
Modifying a Report's Appearance
You can use the Design view to change the way your report looks and to change the way that each section of the report is used. You can do several things to make your report more attractive and useful.
Getting Rid of White Space
As with any published document, too much paper without enough information to fill it can ruin a good report. Generally speaking, most report sections should be only slightly larger than the information displayed in them. You can re-size each section's height by clicking on the section's lower border and dragging. You do not control width by section, but you can increase or decrease the entire report's width by clicking on the right border and dragging.
Adding Page Breaks
Using page breaks is a good way to break information into discrete units. If you want to divide the information in the report header or footer between two or more pages, you use a page break to mark where you want the new page to begin. If you want to force a page break after a particular section of your report, then change the ForceNewPage property for that section. You would use this option if you wanted each Detail displayed on a separate page. It is important to make sure that you insert the page break above or below the information in each section. In most cases, you do not want to split the data in a control.
Adding New Sections
Another quite effective method for jazzing up any report is adding sections. If you want to print introductory information for the report, add a report header that prints only at the beginning of the report. Other section types can be added or removed to improve the report's appearance.
Report Templates
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 without using a Report Wizard , Access uses a template to determine its default characteristics like which sections the report will contain and what are the default property settings for controls and sections. By default, Access provides a report template 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.
Changing the Report Template
- Choose Options from the View menu.
- In the Category box, click on Form & Report Design .
- Select Report Template from the Items box.
- Enter the new report template's name.
If you want to use your report template in another database, you will need to copy the template to that database. If your template is not part of that database, then Access uses the Normal template for any new reports. One glitch in this Access feature is that the program will still list the other template as an option even when you are working in a database that does not contain the report template. Access would default back to the normal template if you chose this new template without copying it to the database.
Sorting Data
When you print a report, one thing that you often want to do is to arrange the data in a particular order. If you are printing out a mailing list, then you might want to sort the names alphabetically or to sort them by increasing Zip Code. You use the Sorting and Grouping box to establish the sort order. You open this in the report's Design view by choosing Sorting and Grouping from the View menu.
You use the top part of the box 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.
Grouping Data
The other part of the Sorting and Grouping box is called Group Properties . This report feature allows you to divide the 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.
Group Headers and Footers
When you specify sort fields in the Sorting and Grouping box, you actually create groups by adding group headers and footers. After selecting the field that you want to group on, you can set the GroupHeader and GroupFooter 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 GroupOn and GroupInterval properties in the Sorting and Grouping box. These properties work hand in hand. If you are making a report that groups records on a Date field, then you can separate a year's records into four quarterly periods by setting GroupOn to Month and GroupInterval to 3.When you group records on a Text field, you have two options for setting the GroupOnproperty: 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.
Using Some Common Expressions in Reports
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 any control that has a ControlSource property can be used. Two common expressions add the current date or page numbers to your report.
Printing the Current Date
You frequently need to print the date on which a report was made. You can use the Now function or the Date function. 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 in any of the available date and time formats. To add either of these functions, enter =Now or =Date() into the ControlSource property. Then, you can choose the date format that you like best.
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 in the control. You also print out a different format to relate the current page to the total number of pages with the Pages function. 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 similar 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, you would enter =Sum([Hours Worked]) in the detail section. If you then grouped employees by department, you would enter =Sum([Hours Worked]) in the group footer.
Note: when you want to total the values in a calculated control, you must repeat the expression used in the original Sum function.