<< . .

. 17
( : 51)

. . >>

Variations include summary and group/total reports.
¦ Columnar reports. These print data as a form and can include totals and graphs.
¦ Mail-merge reports. These create form letters.
¦ Mailing labels. These create multicolumn labels or snaked-column reports.

Tabular reports
Figure 8-1 is a typical tabular-type report in the Print Preview window. Tabular reports (also
known as groups/totals reports) are generally similar to a table that displays data in neat
rows and columns. Tabular reports, unlike forms or datasheets, usually group their data by
one or more field values; they calculate and display subtotals or statistical information for
numeric fields in each group. Some groups/totals reports also have page totals and grand
totals. You can even have snaked columns so that you can create directories (such as
telephone books). These types of reports can use page numbers, report dates, or lines and
boxes to separate information. They can have color and shading and can display pictures,
business graphs, and memo fields, like forms. A special type of tabular report, summary
reports, can have all the features of a tabular report but not print the detail records.

Figure 8-1: A tabular report in the Print Preview window of Access 2003.
Chapter 8 ¦ Understanding and Creating Access Reports 169

Columnar reports
Columnar reports (also known as form reports) generally display one or more records per
page, but do so vertically. Columnar reports display data very much as a data-entry form
does, but the report is used strictly for viewing data and not for entering data. Figure 8-2 is
part of a typical columnar report from the Access Auto Auctions database system in the
Print Preview window.

Figure 8-2: A columnar report showing report controls distributed throughout
the entire page.

Another type of columnar report, known as a form/subform report, generally displays one
main record per page (like a business form) but can show many records within embedded
subforms. An invoice is a typical example. This type of report can have sections that display
only one record and at the same time have sections that display multiple records from the
many side of a one-to-many relationship ” and even include totals.
Part I ¦ Getting Functional with Office 2003

Figure 8-3 shows an invoice report from the Access Auto Auctions database system in the
Print Preview window.

Figure 8-3: An invoice report.

Mailing labels
Mailing labels are also a type of report. You can easily create mailing labels, shown in
Figure 8-4, using the Label Wizard to create a report in Access. The Label Wizard enables
you to select from a long list of Avery label (and other vendors) paper styles, after which
Access correctly creates a report design based on the data you specify to create your label.
After the label is created, you can open the report in design mode and customize it as
Chapter 8 ¦ Understanding and Creating Access Reports 171

Figure 8-4: A typical mailing-label report in the Print Preview window.

The difference between reports and forms
The main difference between reports and forms is the purpose of the output. Whereas forms
are primarily for data entry, reports are for viewing data (either onscreen or in hard copy
form). Calculated fields can be used with forms and can calculate an amount based on the
fields in the record. With reports, you calculate on the basis of a common group of records, a
page of records, or all the records processed during the report. Anything you can do with a
form ” except data input ” can be duplicated by a report. In fact, you can save a form as a
report and then customize the form controls in the Report Design window.
Part I ¦ Getting Functional with Office 2003

The process of creating a report
Planning a report begins long before you actually create the report design. The report
process begins with your desire to view your data in a table, but in a way that differs from
datasheet display. You begin with a design for this view; Access begins with raw data. The
purpose of the report is to transform the raw data into a meaningful set of information. The
process of creating a report involves several steps:
¦ Defining the report layout
¦ Assembling the data
¦ Creating the report design using the Access Report Design window
¦ Printing or viewing the report

Defining the report layout
You should begin by having a general idea of the layout of your report. You can define
the layout in your mind, on paper, or interactively using the Access Report Design
window. Figure 8-5 is a report layout created with Microsoft Word and served as a
design from an analyst to a developer. This served as the basic design for the report
shown in Figure 8-1.Good reports can first be laid out on paper, showing the fields needed
and the placement of the fields.

Figure 8-5: A sample report layout.
Chapter 8 ¦ Understanding and Creating Access Reports 173

Assembling the data
After you have a general idea of your report layout, you should assemble the data needed for
the report. A report can use data from a single database table or from the results of a query
dynaset. You can link many tables with a query and then use the result of the query (its
dynaset) as the record source for your report. A dynaset appears in Access as if it were a
single table.You can select the fields, records, and sort order of the records in a query.
Access treats this dynaset data as a single table (for processing purposes) in datasheets,
forms, and reports. The dynaset becomes the source of data for the report and Access
processes each record to create the report. The data for the report and the report design are
entirely separate. In the report design, the field names to be used in the report are specified.
Then, when the report is run, Access matches data from the dynaset or table against the
fields used in the report and uses the data available at that moment to produce the report.
In this example, you will use data from only the tblProducts table.

Creating a Report with Report Wizards
With Access, you can create virtually any type of report. Some reports, however, are more
easily created than others, when a Report Wizard is used as a starting point. Like Form
Wizards, Report Wizards give you a basic layout for your report, which you can then
Report Wizards simplify the layout process of your fields by visually stepping you through a
series of questions about the type of report that you want to create and then automatically
creating the report for you. In this chapter, you use Report Wizards to create both tabular and
columnar reports.

Creating a new report
You can choose from many ways to create a new report, including the following:
¦ Select Insert_Report from the main menu when the Database window is selected.
¦ Select the Reports object button and press the New toolbar button on the Database
¦ From the Database window, the datasheet, or the query toolbar, click the New
Object down arrow and select Report.
Regardless of how you start a new report, the New Report dialog box appears. The dialog
box in the figure is already filled in with the choices you are about to make.
The New Report dialog box enables you to choose from among six ways to create a report:
¦ Design View. Displays a completely blank Report Design window for you to start
¦ Report Wizard. Helps you create a tabular report by asking you many questions.
¦ AutoReport: Columnar. Creates an instant columnar report.
Part I ¦ Getting Functional with Office 2003

¦ AutoReport: Tabular. Creates an instant tabular report.
¦ Chart Wizard. Helps you create a business graph.
Label Wizard. Helps you create a set of mailing labels.
To create a new report using a Report Wizard, follow these steps:
For the example below, use the tblProducts table:
1. Create a new report by first selecting the Reports object button and then pressing
the New toolbar button.
2. In the New Report dialog box, select Report Wizard.
3. Select the table tblProducts and click OK.
4. Press the OK button to move to the next Report Wizard screen.

Choosing the data source
If you begin creating the report with a highlighted table or from a datasheet or query, the
table or query you are using is displayed in the Choose the table or query box. Otherwise,
you can enter the name of a valid table or query before continuing. You can also choose
from a list of tables and queries by clicking the combo box selection arrow. In this example,
you use the Hospital Report query you saw in Figure 10-5, which creates data for customer
visits on the date 7/11/01.

If you begin creating a report in Design View, you don™t need to specify a table or query in the
New Report dialog box because you can select the Record Source later on from the Properties

Choosing the fields
After you select the Report Wizard and click the OK button, a field selection box appears.
This box is virtually identical to the field selection box used in Form Wizards. In this
example, select the fields from left to right (shown in Figure 8-6).
1. Select the chrCategory field and press the Select Field button (>) to place the field
in the Selected Fields: area.
2. Repeat for the chrProductID, chrDescription, intQtyInStock, curCost,
curRetailPrice, and curSalePrice fields and press the Select Field button (>) each
time to place the field in the Selected Fields: area.
3. Click the Next button when you are through to move to the next wizard screen.

You can double-click any field in the Available Fields list box to add it to the Selected Fields list
box. You can also double-click any field in the Selected Fields list box to remove it from the box.
Access then redisplays the field in the Available Fields list box.
Chapter 8 ¦ Understanding and Creating Access Reports 175

Figure 8-6: Selecting report fields.

You are limited to selecting fields from the original record source you started with. You can
select fields from other tables or queries by using the Tables/Queries: combo box in this wizard
screen. As long as you have specified valid relationships so that Access can link the data, these
fields are added to your original selection and you can use them on the report. If you choose
fields from tables that don™t have a relationship, a dialog box will ask you to edit the
relationship and join the tables. Or you can return to the Report Wizard and remove the fields.
After you have selected your data, click the Next button to go to the next wizard dialog box.

Selecting the grouping levels
The next dialog box enables you to choose which field(s) you want to use for a grouping. In
this example, Figure 8-7 shows the chrCategory field selected as the only group field. This
step designates the field(s) to be used to create group headers and footers. Groups are used
to combine data with common values.
Using the Report Wizard, you can select up to four different group fields for your report;
you can change their order by using the Priority buttons. The order you select for the group
fields is the order of the grouping hierarchy.
Select the chrCategory field as the grouping field and click (>). Notice that the picture
changes to graphically show chrCategory as a grouping field, as shown in Figure 8-7. This
means that data will be grouped or separated by category and also totaled as well if the
report chosen supports summarized footers.
After you select the group field(s), click the Grouping Options button at the bottom of the
dialog box to display another dialog box, which enables you to further define how your
report will use the group field.
You will learn more about groups, headers, and footers later in this chapter.
Part I ¦ Getting Functional with Office 2003

Figure 8-7: Selecting report group fields.

Defining the group data
The Grouping Options dialog box, which is displayed by pressing the Grouping Options ¦
button in the lower-left corner of the Report Wizard screen, enables you to further define the
grouping. This selection can vary in importance, depending on the data type.
The list box displays different values for the various data types:
¦ Text. Normal, 1st Letter, 2 Initial Letters, 3 Initial Letters, 4 Initial Letters, 5 Initial
¦ Numeric. Normal, 10s, 50s, 100s, 500s, 1000s, 5000s, 10000s, 50000s, 100000s.
¦ Date. Normal, Year, Quarter, Month, Week, Day, Hour, Minute.
Normal means that the grouping is on the entire field. In this example, use the entire
Customer Name field. By selecting different values of the grouping, you can limit the group
values. For example, suppose you are grouping on the Product ID field. A typical Product
ID value is CAR-01. The characters to the left of the ” represent the category and the
numbers to the right of the ” are a sequential number. By choosing the Product ID field for
the grouping and then selecting 3 Initial Letters as the grouping data, you can group the
products by their category.
In this example, the default text-field grouping option of Normal is acceptable.
If you displayed the Grouping Options dialog box, click the OK button to return to the
Grouping levels dialog box.
Click the Next button to move to the Sort order dialog box.
Chapter 8 ¦ Understanding and Creating Access Reports 177

Selecting the sort order
Access sorts the Group record fields automatically in an order that helps the grouping make
sense. The additional sorting fields specify fields to be sorted in the detail section. In this
example, Access is already sorting the data by the chrCategory field in the group section. As
Figure 8-8 shows, the data is also to be sorted by Product ID so that the products appear in
alphabetical order in the detail section.

Figure 8-8: Selecting the field sorting order.

The sort fields are selected by the same method that is used for grouping fields in the report.
You can select fields that you have not already chosen to group and use these as sorting
fields. The fields chosen in this dialog box do not affect grouping; they affect only the
sorting order in the detail section fields. You can determine whether the order is ascending
or descending by clicking the button to the right of each sort field, which toggles between
Ascending and Descending.

Selecting summary options
At the bottom of the sorting dialog box is a button named Summary Options. Clicking this
button displays the dialog box shown in Figure 8-9. This dialog box provides additional
options for numeric fields. As you can see in Figure 8-9, all of the numeric and currency
fields are displayed and selected to be summed. Additionally, you can display averages,
minimums, and maximums.
Sum should be checked. You can also decide whether to show or hide the data in the detail
section. If you select Detail and Summary, the report shows the detail data; selecting
Summary Only hides the detail section and shows only totals in the report.
Part I ¦ Getting Functional with Office 2003

Finally, checking the box labeled Calculate percent of total for sums adds the percentage of
the entire report that the total represents below the total in the group footer. If, for example,
you had three products and their totals were 15, 25, and 10, respectively, they would show
30%, 50%, and 20% below their total (that is, 50) ” indicating the percentage of the total
sum (100%) represented by their sum.
Clicking the OK button in this dialog box returns you to the sorting dialog box. There you
can click the Next button to move to the next wizard dialog box.

Figure 8-9: Selecting the summary options.

Selecting the layout
Two more dialog boxes affect the look of your report. The first (shown in Figure 8-10)
enables you to determine the layout of the data. The Layout area provides six layout choices;
these tell Access whether to repeat the column headers, whether to indent each grouping,
and whether to add lines or boxes between the detail lines. As you select each option, the
picture on the left changes to show the effect.
The Orientation area enables you to choose between a Portrait (up-and-down) and a
Landscape (across-the-page) layout. This choice affects how it prints on the paper. Finally,
the check mark next to “Adjust the field width so all fields fit on a page” enables you to
cram a lot of data into a little area. (Magnifying glasses may be necessary!)
For this example, choose Stepped and Landscape, as shown in Figure 8-10. Then click on
the Next button to move to the next dialog box.
Chapter 8 ¦ Understanding and Creating Access Reports 179

Figure 8-10: Selecting the page layout.

Choosing the style
After you choose the layout, you can choose the style of your report from the dialog box
shown in Figure 8-11. Each style has different background shadings, font size, typeface, and
other formatting. As each is selected, the picture on the left changes to show a preview. For
this example, choose Casual (as shown in Figure 8-11). Finally, click the Next button to
move to the last dialog box.

Figure 8-11: Choosing the style of your report.

You can customize the styles, or add your own, by using the AutoFormat option from the Format
menu of the Report Design window and choosing Customize.
Part I ¦ Getting Functional with Office 2003

Opening the report design
The final Report Wizard dialog box contains a checkered flag, which lets you know that
you™re at the finish line. The first part of the dialog box enables you to enter a title for the
report. This title will appear once at the beginning of the report, not at the top of each page.
The default is the name of the table or query you used initially.
Change the report name to rptProductsCh13.
Next, you can choose one of the option buttons at the bottom of the dialog box:
¦ Preview the report
¦ Modify the report™s design
For this example, leave the default selection intact to preview the report. When you click the
Finish button, your report is displayed in the Print Preview window. Name the report
rptProducts. Click Finish to complete the Report Wizard and view the report.

Using the Print Preview window
Figure 8-12 displays the Print Preview window in a zoomed view of page 2. This view
displays your report with the actual fonts, shading, lines, boxes, and data that will be on the
printed report. When the Print Preview mode is in a zoomed view, pressing the mouse
button changes the view to a page preview that shows the entire page.

Figure 8-12: Displaying a report in the zoomed preview mode.
Chapter 8 ¦ Understanding and Creating Access Reports 181

You can move around the page by using the horizontal and vertical scrollbars. Use the Page
controls (at the bottom-left corner of the window) to move from page to page. These
controls include VCR-like navigation buttons to move from page to page or to the first or
last page of the report. You can also go to a specific page of the report by entering a value in
the text box between the previous and next controls.
Figure 8-13 shows a view of the report in the multi-page preview mode of Print Preview.
The sixth icon from the left displays up to six pages at a time. The magnifying glass mouse
pointer selects part of the page to zoom in. In Figure 8-13, you can see a representation of
the printed page. Use the navigation buttons (in the lower-left section of the Print Preview
window) to move between pages, just as you would to move between records in a datasheet.
The Print Preview window has a toolbar with commonly used printing commands.

Figure 8-13: Displaying a report in Multiple Pages Print Preview™s page preview mode.

If, after examining the preview, you are satisfied with the report, select the Printer button on
the toolbar to print the report. If you are dissatisfied, select the Close button to return to the
design window; Access takes you to the Report Design window to make further changes.
Part I ¦ Getting Functional with Office 2003

Viewing the Report Design window
When you click Design View (the left-most button on the toolbar), Access takes you to the
Report Design window, which is similar to the Form Design window. The major difference
is in the sections that make up the report design. As shown in Figure 8-14, the report design
reflects the choices you made using the Report Wizard.

Figure 8-14: The Report Design window.

You may also see the Toolbox, Sorting and Grouping dialog box, property sheet, and Field List
window, depending on whether you pressed the toolbar buttons to see these tools. You learn to
change the design of a report in this chapter. For more detailed information on changing report
design, see Chapters 14, 15, and 16 of the Access 2003 Bible.

You can return to the Print Preview mode by selecting the Print Preview button on the
Report Design toolbar or by selecting the Print Preview option on the File menu. You can
also select Print or Page Setup from the File menu. This menu also provides options for
saving your report.

<< . .

. 17
( : 51)

. . >>