Access 2003
©
Introduction to Report Design
Access 2003 Reports
Princeton University
Office of Information Technology
TABLE OF CONTENTS
CREATING A REPORT IN DESIGN VIEW .................................................................................... 3
BUILDING THE REPORT LAYOUT ............................................................................................... 5
SETTING THE REPORT WIDTH......................................................................................................... 5
DISPLAYING THE FIELD LIST ........................................................................................................... 5
WORKING WITH REPORT SECTIONS ............................................................................................... 6
WORKING WITH REPORT CONTROLS .............................................................................................. 7
SELECTING CONTROLS .................................................................................................................. 7
RE-SIZING CONTROLS ................................................................................................................... 8
DELETING CONTROLS .................................................................................................................... 8
UNDOING YOUR ACTIONS............................................................................................................... 9
ALIGNING CONTROLS ..................................................................................................................... 9
APPLYING ATTRIBUTES ................................................................................................................ 10
CHANGING FONTS & POINT SIZE .................................................................................................. 11
CHANGING THE COLOR OF THE FONT .......................................................................................... 12
ADDING BORDERS ....................................................................................................................... 12
ADDING SPECIAL EFFECTS ........................................................................................................... 13
CHANGING LINE & BORDER COLOR .............................................................................................. 13
CHANGING FILL/BACK COLOR ...................................................................................................... 14
USING THE FORMAT PAINTER ...................................................................................................... 14
SORTING AND GROUPING YOUR REPORT ............................................................................. 16
USING THE SORTING AND GROUPING WINDOW............................................................................. 16
A TIP FOR ARRANGING GROUP HEADINGS .................................................................................... 19
ADDING A CALCULATED CONTROL ........................................................................................ 20
DISPLAYING THE TOOLBOX .......................................................................................................... 20
COMBINING FIELDS WITH A CALCULATED CONTROL ......................................................... 23
SAVING/PREVIEWING A REPORT ............................................................................................. 24
SAVING THE REPORT ................................................................................................................... 24
CLOSING A REPORT .................................................................................................................... 24
PREVIEWING A REPORT ............................................................................................................... 25
Page 2
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
Creating A Report In Design View
1. In the Database window, under Objects, click Reports.
2. Double-click on Create report in design view
Microsoft Access displays the report in Design view
Page 3
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
3. Choose View, Properties from the pull-down menu at the top of the
screen. The Report properties box is displayed. (Note: the properties box
displays properties for whatever section of the report you happened to be
working in. If the properties box does not have Report in the blue area at
the top, click your mouse on the dark gray background in the report design
window.
4. Click on the Data tab in the report properties window. Click the list arrow
next to the Record Source property and select the table or query that you
want to use for the report. Note that if you want to create a report that
uses data from more than one table, you should create a query that
combines the table data first, and then base your report on the query.
The field list for the table or query you have selected is displayed on the screen.
Page 4
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
Building The Report Layout
To build your report, you will:
set the report width and margins
use the Field List to add fields to the report layout; the mouse
use the Format Toolbar and Format Menus to arrange, align and format
the fields
use the Sorting and Grouping dialog box to sort and group your report;
and
use the Toolbox tools to add calculations, dates and page numbers to the
report.
Setting the Report Width
The report design defaults to a layout width of five inches, with top, bottom, left
and right margins of one inch.
To change the report width, drag the edge of the report area with your mouse to
extend or shrink it.
To change margins, choose File, Page Setup from the menu at the top of the
screen.
Displaying The Field List
If you want to quickly add a field to the report, you can select the field from a list
and drag it to the required location.
First you must display the field list, as shown below:
Click on this tool to display the field list.
Page 5
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
A box similar to the following diagram will be displayed:
To place a field from the list onto the report, click on the field name in the list
and drag the field to the location on the report.
Access will add a bound text box at that location for that field.
Working With Report Sections
The report is divided into sections.
The Report Header and Footer appear once, at the beginning (header) and
end (footer) of the report.
Page Headers and Footer repeat on each page.
Section Headers and Footer are used when you have grouped your report by
one or more fields, and they appear at the beginning (header) and end(footer) of
each section on the report.
Objects placed in the Details section are displayed for each record and may be
repeated several times per page.
Page 6
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
Working With Report Controls
There are 3 types of controls on a typical report.
A Bound control displays the contents of a field from a table or query. Bound
controls also have labels attached to them when they are first placed into the
report design. These labels can be altered or deleted without affecting the
bound control.
An Unbound Control displays a literal text value, usually a title or label.
A Calculated Control displays the result of an expression, for example a page
number, a report date, or a formula or functions that calculates a value based on
one or more fields on the report.
Selecting Controls
As you work with a report, you will need to select one or more of the controls to
move them around the report or to change their properties.
To select a control, you must use the pointer tool.
Once you select the pointer, click on the control. Small rectangular handles
appear around the control, as illustrated below:
Some controls, such as the text box shown above, have an attached label that
will also be selected.
Page 7
©2003 EZ-REF Courseware
Princeton University
Office of Information Technology
Access 2003 Reports
The larger handles (black rectangles) are used to reposition the control. If, for
example, you wanted to move the label for the control, you would drag the
move handle for the label.
If you only wanted to move the text box, you would drag the move handle for
the text box. To move both the control and its attached label, place the mouse
pointer over a border until it changes to a small hand, as illustrated in the
diagram below:
Once you have the hand pointer, click and drag both items to their new location
on the report. To select more than one control, hold the Shift key down while
you select additional controls.
Re-Sizing Controls
If you need to make a control larger or smaller, you must first select the control
to view the sizing handles, as shown below:
To resize a control, follow these two steps:
n
o
Place the mouse over one of the smaller sizing
handles.
When the mouse pointer changes to a doublesided arrow, drag the handle in the direction
you need. When you release the mouse
button, the control will be resized.
Deleting Controls
Page 8
©2003 EZ-REF Courseware
Princeton University
Office of Information Technology
Access 2003 Reports
If you no longer need a control, you can easily delete the item by following the
two steps outlined below:
nSelect the object(s) to be removed from the report.
oPress the Delete key.
Undoing Your Actions
If you accidentally deleted the wrong item or moved an object to the wrong spot,
you can easily reverse your last action using the Undo option.
You can undo the last action performed using the mouse:
Click on the Undo button once.
If you click on the down arrow
the last several actions.
(to the right of the tool), you can scroll through
Aligning Controls
As you move controls around on the report, you will realize that it is a tedious
job to align the objects using only your eyes. Fortunately, Access provides an
option that allows you to quickly align several selected objects at once.
The steps for aligning controls are outlined below:
n
o
Select the objects that should be aligned.
Remember to hold Shift down as you click on
each control to be aligned.
Access the following menu:
MENU:
Format
Align ¾
p
Page 9
Select the desired alignment from
the submenu that appears.
©2003 EZ-REF Courseware
Princeton University
Office of Information Technology
Access 2003 Reports
The following alignment options are available:
Left
Used to align the left edges of the
selected controls with the left edge of
the leftmost control.
Right
Used to align the right edges of the
selected controls with the right edge of
the rightmost control.
Top
Used to align the top edges of the
selected controls with the top edge of
the highest control.
Bottom
Used to align the bottom edges of the
selected controls with the bottom edge
of the lowest control.
To Grid
Used to align the upper-left corners of
the selected controls with the grid.
Applying Attributes
As you edit controls, you can change the appearance to add emphasis and
make the report easier to read. This is referred to as formatting. Formatting the
text characters means setting the type style and size of the letters, emphasizing
words with bold, underline or italics. Attributes, such as bold and underline must
be turned on and turned off.
TURNING BOLD ON/OFF
Click on this button to turn Bold on and off.
TURNING ITALICS ON/OFF
Click on this button to turn Italics on and off.
Page 10
©2003 EZ-REF Courseware
Princeton University
Office of Information Technology
Access 2003 Reports
TURNING UNDERLINE ON
Click on this button to turn Underline on and
off.
Changing Fonts & Point Size
A font is a family of characters that have the same
design. The more traditional term for font is typeface.
To change fonts, you select the new font by its name.
To assign a new font, follow the three steps outlined
below:
n
o
p
Select the control to be modified.
Click on the down arrow
to the right of the
Font button on the formatting toolbar, as
illustrated in the diagram below:
Select the desired typeface from the pull-down
list.
CHANGING THE POINT SIZE
To assign a new font size, follow the three steps outlined below:
n
o
p
Page 11
Select the control to be modified.
Click on the down arrow
to the right of the
Size button on the formatting toolbar, as
illustrated in the diagram below:
Select the desired font size from the pull-down
list.
©2003 EZ-REF Courseware
Princeton University
Office of Information Technology
Access 2003 Reports
Changing The Color Of The Font
Although Access defaults to printing your data in
black, you can quickly change the color of the font by
accessing the toolbar, as outlined in the steps shown
below:
n
Select the control to be modified.
o
Click on the down arrow
beside the Font Color icon
(located on the formatting
toolbar).
p
Select the color you
want to use (from
the pull-down list)
for the selected
control.
Adding Borders
You can enhance your report by adding borders around the controls, as shown
in the steps below:
n
o
p
Page 12
Select the control to be modified.
Click on the down arrow
beside the Line & Border Width
icon (on the formatting toolbar).
Select the width of border you
want to use (from the pull-down
list) for the currently selected
control.
©2003 EZ-REF Courseware
Princeton University
Office of Information Technology
Access 2003 Reports
Adding Special Effects
You can assign special effects to the controls that cause them to look
embossed, sunken, raised, shadowed, etched, etc.
Follow the steps shown below to add special effects to the currently selected
control:
n
o
p
Select the control to be modified.
Click on the down arrow
beside the Special Effect icon
(on the formatting toolbar).
Select the type of effect to assign
to the selected control.
Changing Line & Border Color
If you have added a line or border to a control, you can quickly change its color
by following the steps shown below:
n
o
p
Page 13
Select the control to be modified.
Click on the down arrow
beside the Line/Border Color
tool.
Select the color you
want to use (from
the pull-down list).
©2003 EZ-REF Courseware
Princeton University
Office of Information Technology
Access 2003 Reports
Changing Fill/Back Color
To change the fill or background color, follow these steps:
n
o
Select the control to be modified.
Click on the down arrow
beside the
Fill/Back Color icon on the formatting
toolbar.
p
Select the color you
want to use (from
the pull-down list).
Using The Format Painter
Access offers a feature which allows you to copy attributes from one control and
paste them onto another control. This feature can be timesaving for quickly
copying the format of a control.
Follow the steps shown below to use the format painter:
n
o
Select the control containing the attributes to
be copied.
Select the Format Painter tool from the
toolbar. If you plan on formatting more
than one control, double-click on this
tool.
Your mouse pointer changes to a
paintbrush.
p
Page 14
Highlight the control to be formatted. Access
will automatically apply the same formatting
©2003 EZ-REF Courseware
Princeton University
Office of Information Technology
Access 2003 Reports
options you copied. Continue highlighting each
control to be formatted.
q
Page 15
If you double-clicked the icon to begin with, the
Format Painter remains active until you
deactivate it by clicking the tool once (If you
only clicked the tool once, Access deactivates
this feature after the first control is formatted).
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
Sorting And Grouping Your Report
Using The Sorting And Grouping Window
You can easily sort and/or group data in a report. Sorting organizes records by
the values in one or more fields. For example, you can sort a list of products
alphabetically. In reports, you can take sorting one step further and separate
sorted records into groups.
Grouping lets you organize and arrange records by group, such as by region or
salesperson. Groups can be nested so that you can easily identify the
relationships among the groups and find the information you want quickly. You
can also use grouping to calculate summary information, such as totals and
percentages.
When you print a report, you usually want to organize the records in a particular
order. For example, if you’re printing a list of suppliers, you might want to sort the
records alphabetically by company name. When you're working in Design view,
you set the sort order for a report in the Sorting And Grouping window.
To display the Sorting And Grouping window
1.
Open the report in Design view.
2.
On the View menu, click Sorting And Grouping (or click the Sorting
And Grouping
on the toolbar).
You use the top portion of the Sorting and Grouping window to set the sort order
for the records in a report. You can sort on up to 10 fields and expressions, and
you can sort on the same field or expression more than once. For example, in a
five-character field, you can sort in ascending order on the first two characters
and in descending order on the last three characters.
Field/Expression Specify the name of the field or expression on which to
sort. The field or expression in the first row of the Field/Expression
column determines the first level of sorting; the field or expression in the
second row determines the second level of sorting; and so on. For
Page 16
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
example, you could sort a list of employees by last name and then by first
name.
Sort Order: Specify how to sort the data in the field or expression.
Ascending sorts from the lowest value to the highest. Descending sorts
from the highest value to the lowest. For example, ascending order sorts
the values in Text fields alphabetically. It sorts the values in Numeric fields
from 0 to the maximum value.
To create a sort order
3.
Display the Sorting And Grouping window.
4.
In the Field/Expression column, select the field you want, or type an
expression.
5.
When you enter a value in the Field/Expression column, Access sets
Sort Order property to Ascending.
6.
To change the sort order to descending, click Descending in the Sort
Order list.
Page 17
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
To create a group
7.
Display the Sorting And Grouping window.
8.
In the Field/Expression column, select the field you want to group by.
9.
In Group Properties, select a group header, group footer, or both. Group
footers are most often used when you want to create subtotals or record
counts for a group.
After selecting the group header, Access will place a header section on the report
layout. Typically, you would put the field that you are grouping on in this section.
You can set the Keep Together property to avoid having group headings that are
orphaned from the data they belong with.
The Keep Together property can be set for:
Whole Group – Begins each new group on a new page; however, if there are two
small groups in succession, and they will fit on one page, it will place both of
them on the same page.
With First Detail – Keeps the headings together with the first detail record to
avoid orphaned headings; however, if a group is larger than one page, the
headings will not repeat on the additional pages.
As you can see above, using Whole Group or With First Detail settings can leave
you with problems depending on the data you are working with at the time the
report is generated. See the suggestions below under A Tip for Arranging Group
Headings for a strategy to reduce problems with group headings.
Page 18
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
A Tip for Arranging Group Headings
Often when creating heading for fields that are grouped, you have a dilemma
as to where the headings will work best. If you put them in the group header
section of the report, they will not appear on multiple pages of a group. If you
place them in the page header of the report, they will not change if a new group
begins in the middle of a page. Below is a simple-to-use suggestion for
displaying group headers without these problems.
1. Create the group header as described above., and set the Keep
Together property to None.
2. Place the field that you are grouping on into the Detail section of the
report layout (see example below).
Header for ST field
placed in Detail
section
3. Click on the control for the field used for grouping and click the
Properties button.
4. Click on the Format tab in the properties box, locate the Hide
Duplicates property for the control, and set it to Yes.
The group now appears at the beginning of each new group, and repeats at the
top of each page the group occupies.
Page 19
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
Adding a Calculated Control
In addition to adding normal fields to your report, you can also create
mathematical calculations based on numeric fields. For example, if you have
an existing field containing current salaries, you may want to create a report
displaying how a 5% increase would affect those salaries.
The following mathematical operators can be used:
+
*
/
Addition
Subtraction
Multiplication
Division
You can also use functions, such as those listed below:
SUM
AVERAGE
MAXIMUM
MINIMUM
COUNT
When using the Report Wizard, you are given the option to summarize fields
and define what type of calculation should be used. This information will be
used to automatically create the calculated control.
To manually create a calculated control, you will need to assign a function or
formula to an existing control that has been placed within your report.
The placement of the control determines what records will be calculated, as
outlined below:
Detail Section
Calculates the data for the current record only.
Group Footer
Calculates the data for the current group only.
Page Footer
Calculates the data for all records on a page.
Report Footer
Calculates the data for all records in a report.
Before you being, you will need to turn on the Toolbox.
Displaying The Toolbox
Page 20
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
Access provides a toolbox while in report design mode that provides all the
controls and objects needed for customizing.
If the toolbox does not appear on your screen, click on this tool once to display
the toolbox.
Click on the tool you need and place the object on the
report.
If you plan on using a tool more than once, doubleclick on it before you begin and you will be able to add
more than one of that control - without having to
select the tool each time.
If you click on the down arrow
beside the toolbox
name, you can add or remove buttons from the
toolbar.
Page 21
©2003 EZ-REF Courseware
Princeton University
Office of Information Technology
Access 2003 Reports
Follow these steps to place a calculated control in your report:
1. Move to the section where you want to add the control.
2. Insert a text control using the Text Box button from the toolbox..
3. Select the text box and click inside the box.
4. Start the calculation by typing an equal (=) sign, followed by the function or
formula you want to create.
5. To include a field within your calculation, the field name must be enclosed
within square brackets [ ] so that Access knows where to get the data
from, as illustrated in the example below:
=[Salary]*1.05
6. To combine text with your calculation, enclose the text within quotation
marks, as illustrated in the example below:
=“Projected Salary Increase for” & [Employee] & “: “& [Salary]*1.05
7. The ampersand (&) is required to combine the various parts of the
calculation. Everything within quotes is printed verbatim and fields are
placed within square brackets.
Using this example, Access would print the following:
Projected Salary Increase for John Jones: 87,000
You can also use these predefined fields:
[Page]
[Pages]
Date()
Time()
Now()
Displays the current page number
Displays the total number of pages
Displays the current date
Displays the current time
Displays the current date and time
8. When done, press Enter.
You should see the formula displayed within the text box. Switch to the
Preview mode to actually view the result of the calculation.
Page 22
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
Combining Fields with a Calculated Control
You can combine the contents of two or more fields by using a calculated control.
1. Move to the section where you want to add the control.
2. Insert a text control using the Text Box button from the toolbox.
3. Select the text box and click inside the box.
4. Click the Properties button on the report toolbar.
5. Click into the Control Source property in the properties box.
6. Start the calculation by typing an equal (=) sign, Enter the name of the first
field (remember the field name must be enclosed within square brackets [
]) followed by an ampersand
7. Add spacing and/or punctuation by enclosing them in quotation marks.
Follow this with another ampersand.
8. Repeat the last two steps for each field and each set of spacing or
punctuation you wish to add.
For example, to create a last name, first name listing
of names from fields called lastname and firstname:
= [lastname] &", "& [firstname
Another example using address fields:
= [city] &", "& [state] &" "& [zip]
Page 23
©2003 EZ-REF Courseware
Princeton University
Office of Information Technology
Access 2003 Reports
Saving/Previewing A Report
As you can see, creating a report can be an involved task. Designing a decent
report can take several hours or even several days depending on the complexity
of the report. Therefore, you should save the report often. Once a report has
been saved, you can open the report whenever you need it.
Saving The Report
Click on this tool to save the report.
Access will automatically assign the name of the table
to the new report. If you create a second report using
the same table and save it, Access will add the
number 1 after the table name. For example, if you
created three reports using the “Clients” table, the
reports would be named Clients, Clients1, and
Clients2.
Closing A Report
If you are finished with a report, you can close
it by clicking on the close button (located in the
upper right corner of the report). If the latest
changes have not been saved, Access will
warn you and give you a chance to save them.
Page 24
©2003 EZ-REF Courseware
Access 2003 Reports
Princeton University
Office of Information Technology
Previewing A Report
Once you have saved your report, you can use it repeatedly without having to
recreate the design of the report. To use a previously saved report, you must
preview the report.
You open reports the same way you open tables - from within the database
window, as shown below:
Click on this icon to list the available
reports.
Select the report you need and click on
view the data, or double-click the report name.
to
If you want to make changes to the report, click on
to enter the design mode.
Depending on what you selected, you will be either
viewing or modifying the report.
Page 25
©2003 EZ-REF Courseware