Navigation

Previous
Next
Homepage
FAQ
What's new
Sitemap

Topics

Access
Tables
Queries
Exercices on queries
Forms
Chart form
Reports
Label reports
Macros
Relations

Tutorials

Word
Excel

Excel 2007/2010/2013
Excel 2010
PowerPoint
Access

Others

Demonstration files
Texte en français

Contact

By e-mail
Join our Newsletter

Access - Reports

Before we begin
Introduction
Types of reports
Creating a simple report with the assistant
Change a report
Creating a group

Before we begin

For these 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 you put to paper. It's possible to analyze 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.

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 wizards 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 another) 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 consistsd 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 picture.
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 picture 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 " List of 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 view ( ).
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, " areas of text, that are really fields resulting from tables or from queries, from rows, from boxes, from calculated fields and several others.

Sections

Besides, these "controls" you will find in various areas.

Headers

Feet

Every heading in its equivalent in the end of the report.

Section details

Between the various headings and the feet of areas, there is a section detail. It's in this area that will be shown the data of each of the records.

Add a calculated field

As for the queries and the forms, you may add calculated fields that answer better your needs. For example, there is no standard deviation or the other Formulas or functions to analyze 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're already not it, put yourselves in Design view by pressing on the button.
If the toolbar is not shown, press the button.
Enlarge the footer area of group office.
From the toolbar, press the button bums around of text .
Click towards your choice in the area 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 area of text ( independent field).
Write the following formula: =sum( salary+commission ).

Because there is already a total of salaries and a total of commissions in this area, you would have been able to write in the place of the formula = Sum DeSalaire + Sum DeCommission.

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 view 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're 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 functionne!

The next exercise consists in copying the formula preassignor, =sum( salary+commission ), in the footer of the report.

Click on the new formula in the footer of the office group area.
From the Edit menu, select the Copy option.
Click in the footer area 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 area 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, you must put the sign "=" in front of the formula. So, to make the sum incomes, you must use the formula =sum([salary] + [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 areas. 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 record, a group or a report.

Creating a group

It's possible any time to change the sort order 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 row of the First name field.
Press the button with the triangle pointing down at the end of the first box of the row.

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 regroup and changing the sort order. The next exercise consists of removing a group.

Click on the grey box to the left of the field Poste.
Press the Delete key.