To realize the exercises of this Web page, you need the Employees table. You will find it in the database demoacc1.mdb or demoacc2.mdb on the demonstration files Web page. For those that use Access on 2000, you'll find on this same page, files demoa2k1.mdb or demoa2k2.mdb.
Introduction
A report is a representation of the records of the database that is make paper. It's possible to analyse a synthesis from these data that would be more difficult to have otherwise. Besides, it's easier to sort out and to group together the data about relevant fields.
Types of reports
Access can help you during the creation of reports.
Standard:
View the records of the database in the form
of report.
Graph:
To represent diagrammatically the records of
the database. An example of a chart form is demonstrated
on this site.
Labels:
To prepare labels for the mailing or conference.
An example of a report
labels is demonstrated on this site. It's moreover the following page
of this section of the site.
Creating a simple report with the assistant
The example that follows is to demonstrate how to create you a report by
using an assistant by looking at each of the stages of the creation of the report.
One looks also at the possibilities, in each of these stages.
Click
on the reports tab .
Press the New button.
Access offers you several ways to create a new report. You can use the Mode
creation to begin from zero a report. You can also use the assistants to create
reports by answering some appropriate questions. The immediate reports, that
show all the fields of a table or a query under the format of columns (a field
below the other one) or of table (a field next to another), don't ask questions.
They generate a report that shows all the fields of the table in the order that
appears in the structure of the table or the query. The chart assistant allows
to generate a chart from the fields of numeric or monetary type. And to end,
the assistant labels you help to generate a report consisted of label for various
situations: of labels of letters in labels for presentations.
Of
the list, select the Report Assistant option. Among
the list of available tables and queries, select the table "Employés"
(employees). Press
the Next button.
The assistant asks you then that are the fields that you need in your report.
pay attention to the order of the fields that you select. It's in this order
that they will appear in your new report. It's also possible to select
fields of several tables or queries. It's enough to choose the table or the
query from the list. add then one or several fields of your choice to the
list of the selected fields.
Of
the list of the available fields, select, in order, the fields Name, First name, Salaraire (salary), commission, office (office), Poste
(occupation) by clicking the field and by pressing on the button
. Press
the Next button.
The assistant asks you if you want to group together the records on a field
in particular. You can also make " under groups ", or rather groups inside groups.
Of
the list of the fields of the report, select the field office. Press
the button
.
The outline of the presentation in the right-hand side of the window will change. It's now going to show fields grouped together with regard to
the field office. So, every time the value of the field office
will change, a new group will be generated.
Press the Next button.
The assistant asks you how you want to group together the records. want to
group together in a normal way or only on the first letters contained in the
field.
Leave
the option to "Normal" Press the OK button.
The assistant asks you if you want to sort out the records according to one
or several fields. You can sort out the records until on four fields. The field
in the first box has priority over the second and so on. So, you can
choose if you want to show the records in increasing or lessening order by pressing
on the button of highly-rated right of the name of the field.
Select
of the list the field "name" for the first sorting.
The employees will be sorted out according to the name.
Select
of the list the field "First name" for the second sorting.
Besides being sorted out on the name, the records will be also sorted out
on the First name. So, for the same name, the records will be also sorted
out on the First name of these persons. One of the most advantageous options
of reports is the possibility to generate automatically a synthesis of the results.
Press the button Options of synthesis.
From the options of synthesis, you can choose from the most frequent mathematical
operations. If need be, you can modify the report to add the other operations
such as the number of records, the standard deviation and so on. This operation
will be demonstrated farther on this page.
Select
the same options as those marked in the previous image. Press the OK button. Press the Next button.
The assistant asks you then for that type of presentation what you want for
your report ? You can also decide on the orientation of the paper as well as
to force all the fields on the same sheet.
Select
the same options as on the image above. Press the Next button.
The assistant asks you a last question on the presentation of the report. This
time, it's about the presentation of the fonts of the report. The assistant
offers you several ways to present the records in the form of text. click
the name of each of the styles to have a preview of the final result.
For the purpose of this exercise, select the "Administrative" type. Press the Next button.
It remains only to add the main title of the report and to decide if you later
want to have a preview of this one or to change it at once.
Write
in the box of the title of the report " Liste des employés ". Press the Finish button.
Here is the first part of the final result.
It's now time to look in detail at the composition of the report.
Change a report
It's also possible to change a report better to answer your needs. In time,
the report can need to change better to answer your expectations.
From
the outline of the report, press the button Design mode ( ).
From
the window Data base, select the report to be modified and press the Modify button.
The objects of a report
There are several objects, or controls in Access's jargon, that compose a
report. Among some of these, there are "titles", that are of the free text,
" zones of text, that are really fields resulting from tables or from queries,
from lines, from boxes, from calculated fields and several others.
Sections
Besides, these "controls" you will find in various zones.
Headers
Header of the report:
This part meets itself at the very beginning
of the report. One finds there generally the title of the report.
Header of the page:
This part meets itself at the beginning of each
of the pages of the report. One finds there generally the name of each of
the fields that meets itself in the report.
Header of the group:
This part meets itself at the beginning of each
of the groups of the report. One finds there generally the description of
the group.
Feet
Every heading in its equivalent in the end of the report.
Foot of the report:
This part contains generally the cumulative
or the synthesis of the report.
Foot of page:
This part contains the cumulative of the page
as well as the number of page.
Foot of group:
This part contains generally the cumulative
or the synthesis of a group.
Section details
Between the various headings and the feet of zones, there is a section detail.
It's in this zone that will be shown the data of each of the records.
Add a calculated field
As for the queries and the forms, it's possible to add calculated fields
that answer better your needs. For example, there is no standard deviation
or the other Formulas or functions to analyse certain ratios among
your data.
The next part consists in adding a calculated field that calculates the total
of the incomes of a group. By income, one implies here the total of salaries
and commissions of the employees.
If
you are already not it, put yourselves in Design mode by pressing on the button.
If
the toolbar is not shown, press the button.
Enlarge
the footer zone of group office. From
the toolbar, press the button
bums around of text . Click
towards your choice in the zone foot of group " office ".
An independent field and a title will appear.
Click
in the box of the title and change the text "Incomes". Click
in the zone of text ( independent field). Write
the following formula: =sum( salaire+commission ).
Because there is already a total of salaries and a total of commissions
in this zone, you would have been able to write in the place of the formula
= SommeDeSalaire + SommeDeCommission.
It's necessary to pay attention not to make errors during the writing of the
formula. Otherwise, at the time of showing the report, Access will ask you of the data about a field that does not exist. For example, I changed
one of the Formulas so that it calculates the income by using the field commisssion
with three " s ". This field does not exist. But Access will ask you
for the value of this field before showing the report.
An outline of the report ( ) will show you the result. At the end of
every group, there is now a total of incomes.
You can create more elaborated calculated fields by using functions
of Access. If you know the functioning of the function, you can directly
write it. Otherwise, you can use the expression generator to fetch the list
of the functions.
Return
to Design mode by pressing on the button. Place
the cursor on the calculated field that you have just created. Press
the right mouse button. Select
the Properties option. OR Of
the standard toolbar, press the button.
Click
in the box next to the option Source of control. Click on the button that has just appeared to the end of the box.
You are now in the expression generators.
Of
the first column, double-click on the shirt having one more when it's written
Functions.
The second column included lists it categories of the functions. The third
column included lists it of all the available functions for Access. You can
then choose from the list the function, or the combination of functions, that
answers your needs. have by fear to experiment. In the worst, the calculation
will not work. It will be enough to re-try until it fonctionne!
The next exercise consists in copying the formula preassignor, =sum( salaire+commission
), in the footer of the report.
Click
on the new formula in the footer of the office group zone. From
the Edit menu, select the Copy option. Click
in the footer zone of the report. From
the Edit menu, select the Paste option. Move
the field next to the existing fields. Make
a preview of the report by pressing on the button .
At the end of the report, there is a new field that shows the cumulative of
the incomes of the employees. Although it's about the same formula, it
has no same area as office formulates it in the zone of group. The first
show the result of all the records whereas the second fact only the cumulative
a group at the same moment.
Add a calculated field
It's always possible to add a field that is contained in the table or the
query that created the report. But it's also possible to add calculated
fields such as demonstrated during the creation of a query or a form. As for
the form, it's necessary to put the sign "=" in front of the formula. So, to
make the sum incomes, it's necessary to use the formula =sum([salaire] +
[commission]).
It's always possible to improve the presentation of the report but this gives
you an idea in the way that the records will be shown in the report.
It's also possible to copy the formula of incomes and to put it in the other
zones. If you copy the formula and put it in the section Details, the formula
will show the income of each of the records. If you copy the same formula
to put it in the foot of the group office, the result would be the sum incomes
by office. The formula of incomes in the foot of report would show the total
sum of the incomes of the company. The place where is located the formula determines
the vast of this one, that are for a recording, a group or a report.
Creating a group
It's possible any time to change the order of the sortings of fields as well
as the groups of the report. The next part consists in adding a group to the
report as well as to explain at the same time the difference between a sorting
and a group in the report.
From
the Edit menu, select the option sort out and group.
From this screen, you can determine on that field you want to sort out and
to group together the records of the report. The window shows that the report
is at present grouped together on the field office. So, the records are
sorted out on fields Name and First name. The exercise consists
in adding a group on the field Poste. So, besides being grouped together
by office location, the records will be divided by the occupation of the employee.
Here are the necessary stages to add the group Poste.
Place
the cursor on the fourth list of fields, just man below the line of the First name field. Press the button with the triangle pointing down at the end of the first box
of the line.
Of
the list of fields, select the field Poste.
Among
the list of the properties of the field, select the Yes option to show
the header of the group footer.
As soon as you select the one or other one of these two options, the field
is used to group together the records instead of sorting out them only. The
symbol appears
in front of the name of the field to indicate that it's about a group on this
field. So two new sections will appear in the report, that is the heading
of the new group Poste as well as its foot of page.
If you make a preview of the report, you'll see that the result is not really
interesting. The field Poste fact a group on each of the records instead
of group together them. The reason is that the field posts are in fourth position
from the sortings and the groups. The next stage consists in changing the order
of the sortings and the groups by placing the field Poste in second position.
Click on the grey box to the left of the field Poste. Press and hold the left mouse button and move the field in second position
in the list of fields.
The outline of the report shows now the data by office location and by occupation
of the employees.
Remove a group
You saw how adding a regroupement and changing the order of the sortings. The
next exercise consists of removing a group.
Click on the grey box to the left of the field Poste. Press the Delete key.
Click
on the reports tab 
.
button
. 
).
button.
button
bums around of text . 
button.
.
appears
in front of the name of the field to indicate that it's about a group on this
field. So two new sections will appear in the report, that is the heading
of the new group Poste as well as its foot of page. 