Share this page
Access - Reports
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.
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.
Access can help you during the creation of reports.
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.
on the reports tab .
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 consistsd of label for various situations: of labels of letters in labels for presentations.
the list, select the Report Assistant option.
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.
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
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.
the list of the fields of the report, select the field office.
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.
the option to "Normal"
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.
the same options as those marked in the previous picture.
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.
the same options as on the picture above.
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.
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.
in the box of the title of the report " Liste of employés ".
Here is the first part of the final result.
It's now time to look in detail at the composition of the 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.
the outline of the report, press the button creation mode ( ).
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.
Besides, these "controls" you will find in various areas.
Every heading in its equivalent in the end of the report.
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 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.
you are already not it, put yourselves in creation mode by pressing on the button.
An independent field and a title will appear.
in the box of the title and change the text "Incomes".
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.
to creation mode by pressing on the button.
in the box next to the option Source of control.
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 functionne!
The next exercise consists in copying the formula preassignor, =sum( salary+commission ), in the footer of the report.
on the new formula in the footer of the office group area.
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, 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([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 recording, a group or a report.
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.
the cursor on the fourth list of fields, just man below the row of the First name field.
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.
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 regroupment 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.
|You like what you read?
Share it with your friends.