<< . .

. 18
( : 51)



. . >>



Printing a Report
You can print one or more records in your report, exactly as they look onscreen, using one of
these methods:
¦ Click File_Print in the Report Design window.
¦ Click the Print button in the Preview window.
¦ Click File_Print in the Database window (with a report highlighted).
Chapter 8 ¦ Understanding and Creating Access Reports 183

If you select File_Print, a standard Microsoft Windows Print dialog box appears. You can
select the print range, number of copies, and print properties. If you click the Print button,
the report goes immediately to the currently selected printer without displaying a Print
dialog box.


Saving the Report
You can save the report design at any time by selecting File_Save, or File_Save As, or
File_Export from the Report Design window, or by clicking the Save button on the toolbar.
The first time you save a report (or any time you select Save As or Export), a dialog box
enables you to select or type a name.



Starting with a Blank Form
There are many tools available in the Report Design window. When you create reports, you
use some of these tools in a slightly different manner from the way they are used to create
forms. Therefore, it is important to review some of the unique report menus and toolbar
buttons.
You can view a report in three different views: Design View, Layout Preview, and Print
Preview. You can also print a report to the hard copy device defined for Microsoft Windows.
This chapter focuses on the Report Design window.
The Report Design window is where you create and modify reports. The empty Report
Design window, shown in Figure 8-15, contains various tools, including the Toolbox.




Figure 8-15: The Report Design window, showing the Toolbox.
Part I ¦ Getting Functional with Office 2003
184


The Design Window toolbar
The Report Design toolbar is shown in Figure 8-16. You click the button you want for quick
access to such design tasks as displaying different windows and activating wizards and
utilities. Table 8-1 summarizes what each item on the toolbar does. (The table defines each
tool from left to right on the toolbar.)



Figure 8-16: The Report Design toolbar.

The Report Design toolbar is distinct from the Format toolbar. To make such changes as font
selection and justification, you must first make sure that the Formatting (Form/Report)
design toolbar is displayed.

Table 8-1
The Design View Toolbar
Toolbar Item Description
Report View button Drop-down box displays the three types of views available
Save button Saves the current report design

File Search button Finds text within a database or on your computer

Print button Prints a form, table, query, or report

Print Preview button Toggles to print preview mode

Cut button Removes selection from the document and adds it to the
Clipboard

Copy button Copies the selection to the Clipboard

Paste button Copies the Clipboard contents to the document

Format Painter button Copies the style of one control to another

Undo/Redo button Undoes/redoes previous commands

Insert Hyperlink button Inserts hyperlink

Field List button Displays or hides the Field List window

Toolbox button Displays or hides the Toolbox

Sorting and Grouping
button Displays or hides the Sorting and Grouping box

AutoFormat button Applies a predefined format to a form or report
Continued
Chapter 8 ¦ Understanding and Creating Access Reports 185


Table 2-8 (continued)
Toolbar Item Description

Code button Displays or hides the Module window

Properties button Displays the properties sheet for the selected item

Build button Displays the Builder or Wizard for selected control or item

Database Window
button Displays the Database window

New Object button Creates a new object

Microsoft Access
Help button Displays Access Help



Note
The tools on the Report Design screen are virtually identical to the Form Design tools.


Banded Report Writer Concepts
In a report, your data is processed one record at a time. Depending on how you create your
report design, each data item is processed differently. Reports are divided into sections,
known as bands in most report-writing software packages. (In Access, these are simply
called sections.) Access processes each data record from a table or dynaset, processing each
section in order and deciding (for each record) whether to process fields or text in each
section. For example, the report footer section is processed only after the last record is
processed in the dynaset.
A report is made up of groups of details ” for example, as shown in Figure 8-17, all the
products sold by category. Each group must have an identifying group header, which for the
first category in this example is Minivans. Each group also has a footer where you can
calculate the total cost and profit for each category. For Minivans, the total profit is
$17,063. The page header contains column descriptions; the report header contains the
report title. Finally, the report footer contains grand totals for the report, and the page footer
prints the page number.
The Access sections are listed below:
¦ Report header. Prints only at the beginning of the report; used for title page.
¦ Page header. Prints at the top of each page.
¦ Group header. Prints before the first record of a group is processed.
¦ Detail. Prints each record in the table or dynaset.
Part I ¦ Getting Functional with Office 2003
186

¦ Group footer. Prints after the last record of a group is processed.
¦ Page footer. Prints at the bottom of each page.
¦ Report footer. Prints only at the end of a report after all records are processed.
Figure 8-17 shows these sections superimposed on a report.




Figure 8-17: Typical Report Writer sections.


How sections process data
Most sections are triggered by changes in the values of the data. Table 8-2 shows the records
that make up the dynaset for the Products Summary Report (Yes indicates that a section is
triggered by the data).
Chapter 8 ¦ Understanding and Creating Access Reports 187


Table 8-2
Processing Report Sections
Category Product Report Page Category Detail Category Page Report
Name Name Header Header Header Footer Footer Footer
Minivans Mini-03 Yes Yes Yes Yes No No No

Minivans Mini-101 No No No Yes No No No

Minivans Mini-102 No No No Yes No No No

Minivans Mini-103 No No No Yes No No No

Minivans Mini-104 No No No Yes No No No

Minivans Mini-105 No No No Yes No No No

Minivans Mini-115 No No No Yes Yes No No

Motor
Homes Mot-01 No No Yes Yes Yes No No

SUV SUV-076 No No Yes Yes No No No

SUV SUV-101 No No No Yes No No No

SUV SUV-102 No No No Yes No No No

SUV SUV-103 No No No Yes No No No

SUV SUV-104 No No No Yes No No No

SUV SUV-111 No No No Yes No No No

SUV SUV-112 No No No Yes No No No

SUV SUV-113 No No No Yes No No No

SUV SUV-568 No No No Yes Yes Yes No


As you can see, Table 8-2 shows 17 records. Three groups of records are grouped by the
category. There are seven Minivans, one Motor Homes, and nine SUVs. Each record in the
table has corresponding columns for each section in the report. “Yes” means that the record
triggers processing in that section; “No” means that the section is not processed for that
record. This report is only one page, so it is very simple.
The report header section is triggered by only the first record in the reports dynaset. This
section is always processed first, regardless of the data. The report footer section is triggered
only after the last record is processed, regardless of the data.
Part I ¦ Getting Functional with Office 2003
188

Access processes the page header section after the report header section for the first record
and then every time a new page is started. The page footer section is processed at the bottom
of each page and after the report footer section of the last page.
Group headers are triggered only by the first record in a group. Group footers are triggered
only by the last record in a group. Notice that the Mot-01 Motor Homes record triggers both
a group header and a group footer because it is the only record in a group. If three or more
records are in a group, only the first or the last record can trigger a group header or footer;
the middle records trigger only the detail section.
Access always processes each record in the detail section (which is always triggered,
regardless of the value of a data item). Most reports with a large amount of data have many
detail records and significantly fewer group header or footer records. This small report has
as many group header and footer records as it has detail records.

The Report Writer sections
Figure 8-18 shows what a report design looks like in Access. It is the Report Design window
for the Products Summary Report. As you can see, the report is divided into seven sections.
The group section displays data grouped by Categories, so you see the sections chrCategory
Header and chrCategory Footer. Each of the other sections is also named for the type of
processing it performs.




Figure 8-18: The Report Design window.

You can place any type of text or field controls in any section, but Access processes the data
one record at a time. It also takes certain actions (based on the values of the group fields, the
location of the page, or placement in the report) to make the bands or sections active. The
example in Figure 8-18 is typical of a report with multiple sections. As you learned, each
section in the report has a different purpose and different triggers.
Chapter 8 ¦ Understanding and Creating Access Reports 189


Page and report headers and footers must be added as pairs. To add one without the other,
Note
after the section is added, resize the section you don™t want to a height of zero or set its Visible
property to No.

Caution
If you remove a header or footer section, you also lose the controls in those sections.


Report header section
Controls in the report header section are printed only once at the beginning of the report. A
common use of a report header section is as a cover page or a cover letter or for information
that needs to be communicated only once to the user of the report.
You can also have controls in the report header section print on a separate page, which
enables you to create a title page and include a graphic or picture in the section. There is a
Force New Page property in the Report Header that can be set to After Section that will place
the information in the Report Header into a separate page.
In Figure 8-17, the report header section is not used.

Note
Only data from the first record can be placed in a report header.


Page header section
Text or field controls in the page header section normally print at the top of every page. If a
report header on the first page is not on a page of its own, the information in the page header
section prints just below the report header information. Typically, page headers serve as
column headers in group/total reports; they can also contain a title for the report. In this
example, placing the Products Summary report title in the Page Header section means that
the title appears on every page.
The page header section shown in Figure 8-18 also has lines above and below the label
controls. Each of the report™s label controls is separate and each can be moved or sized
individually. You can also change special effects (such as color, shading, borders, line
thickness, font type, and font size) for each text control.
Both the page header and page footer sections can be set to one of four settings (this setting
can be found in the Report™s properties, not the section properties):
¦ All Pages. Both the page header and page footer print on every page.
¦ Not with Report Header. Neither the page header nor footer prints on a page with
the report header.
¦ Not with Report Footer. The page header does not print with the report footer. The
report footer prints on a new page.
¦ Not with Report Header/Footer. Neither the page header nor the footer prints on a
page with the report header or footer.
Part I ¦ Getting Functional with Office 2003
190


Group header
Group headers sections normally display the name of the group. Access knows when all the
records in a group have been displayed in a detail section when the group name changes. In
this example, the detail records are about products and their costs and profits. The group
header field control chrCategory tells you that these products are of a specific category type.
Group header sections immediately precede detail sections.
It is possible to have multiple levels of group headers and footers. In this report, for
example, the data is only for categories. However, in some reports you might have groups of
information with date values. You could group your sections by year or month and year, and
within those sections by another group such as category.

To set group-level properties such as Group On, Group Interval, Keep Together, or something
Note
other than the default, you must first set the Group Header and Group Footer property (or both)
to Yes for the selected field or expression. You will learn about these later in the chapter.


Detail section
The detail section processes every record in the data and is where each value is printed. The
detail section frequently contains a calculated field such as profit that is the result of a
mathematical expression. In this example, the detail section simply displays information
from the tblProduct table except for the last control. The profit is calculated by subtracting
the value of curCost from the value of curSalePrice.

You can tell Access whether you want to display a section in the report by changing the section™s
Tip Visible property in the Report Design window. Turning off the display of the detail section (or by
excluding selected group sections) displays a summary report with no detail or with only certain
groups displayed.

Group footer
You use the group footer section to calculate summaries for all the detail records in a group.
In the Products Summary report, the expression =Sum([curSalePrice] - [curCost]) adds all
the calculations of Sale Price ” Cost for a specific category. In the Minivans group, this
expression sums the seven records. This type of field is automatically reset to 0 every time
the group changes.

Tip
You can change the way summaries are calculated by changing the Running Sum property of
the field box in the Report Design window.

Page footer
The page footer section usually contains page numbers or control totals. In very large
reports, you may want page totals as well as group totals (such as when you have multiple
pages of detail records with no summaries). For the Products Summary Report, the page
Chapter 8 ¦ Understanding and Creating Access Reports 191

number is printed by combining the text Page, and built-in page number controls show Page
x of y where x is the current page number and y is the total number of pages in the report. A
text box control with the following expression in the Control Source property can be used to
display page number information.
=“Page: ” & [Page] & “ of ” & [Pages]
(which keeps track of the page number in the report).
You can also print the date and the time printed. Figures 8-18 and 8-19 show the date printed
in the Page Footer section as well as the page numbers.

Report footer
The report footer section is printed once at the end of the report after all the detail records
and group footer sections are printed. Report footers typically display grand totals or other
statistics (such as averages or percentages) for the entire report. The report footer for the
Products Summary report uses the expression =Sum with each of the numeric fields to sum
the amounts.

Note
When there is a report footer, the page footer section is printed after the report footer.

The Report Writer in Access is a two-pass report writer, capable of preprocessing all records
to calculate the totals (such as percentages) needed for statistical reporting. This capability
enables you to create expressions that calculate percentages as Access processes those
records that require foreknowledge of the grand total.



Creating a New Report
Fundamental to all reports is the concept that a report is another way to view the records
in one or more tables. It is important to understand that a report is bound to either a single
table or a query that brings together data from one or more tables. When you create a
report, you must select which fields from the query or table you want to see in your report.
Unless you want to view all the records from a single table, bind your report to a query.
Even if you are accessing data from a single table, using a query lets you create your
report on the basis of a particular search criterion and sorting order. If you want to access
data from multiple tables, you have almost no choice but to bind your report to a query. In
the examples in this chapter, all the reports are bound to a query (even though it is
possible to bind a report to a table).

Access lets you create a report without first binding it to a table or query, but you will have no
fields on the report. This capability can be used to work out page templates with common text
Note
headers or footers such as page numbering or the date and time, which can serve as models for
other reports. You can add fields later by changing the underlying control source of the report.
Part I ¦ Getting Functional with Office 2003
192

Throughout this chapter, you learn the tasks necessary to create the Products Display
Report (the partial first page is shown in Figure 8-19). In this chapter, you design the basic
report, assemble the data, and place the data in the proper positions. You can learn more
about enhancing your reports by adding lines, boxes, and shading so that certain areas
stand out in Wiley™s Access 2003 Bible, chapter 14.
As with almost every task in Access, there are many ways to create a report without
wizards. It is important, however, to follow some type of methodology, because creating a
good report involves a fairly scientific approach. You should create a checklist that is a set
of tasks that will result in a good report every time. As you complete each task, check it
off your list. When you are done, you will have a great-looking report. The following
section outlines this approach.




Figure 8-19: The Products Summary report.
Chapter 8 ¦ Understanding and Creating Access Reports 193


Creating a new report and binding it to a query
The first step is to create a new report and bind it to the tblProducts table. Follow these steps
to complete this process:
1. Press F11 to display the Database window if it is not already displayed.
2. Click the Reports object button.
3. Click the New toolbar button. The New Report dialog box appears.
4. Select Design View.
5. Click the combo box which label starts with Choose a table or query. A drop-down
list of all tables and queries in the current database appears.
6. Select the tblProducts table.

<< . .

. 18
( : 51)



. . >>