Access Tutorial - Report Creation

Covered in this section:

In addition to sorting records in a database or querying records that meet specific conditions, most database applications allow users to create reports. A report can contain explanatory headings, grouped records, and summary data. Reports can also be combined with queries. For example, you might create a report to explain the salaries of senior employees. But, you might combine this report with queries that select employees by different age ranges.

Defining a Report

To define a report, you go to the Database window and click on Reports under the Objects menu. Now double click on Create Report by using wizard.

This window is similar to the form wizard. It allows you to select the fields you want in your report. In the first dropdown menu, you can choose where the fields for your report will be obtained. In this example, the fields will be from Query1.
Under the Available Fields, you get to choose the fields you want. Highlight the field and click on the > to bring it to the Selected Fields window.
Transfer the following fields to the selected fields window: Student #, Test Mark, Test Grade, Test Weight, and Weighted Mark. You should have created these fields during the Query Tutorial. If you haven't created these fields do so now then come back. Once the above fields have been selected, click on Next.

Another window appears which will let you create groupings of the selected fields.
Say you want a report to show each student’s test marks, test grade, test weight, and weighted mark by the student’s student #, then Student # will have the highest priority. Notice that if you select a field for grouping the it becomes automatically sorted.
Highlight Student # and click on >. Click on Next when you are done.

This window allows you to sort your report. You can sort by more than just one field.
Click on the first drop down menu and select Test Mark. Now Test Mark is used for sorting the report. If you want to sort by another field, click on the second drop down menu and select the field you want to be sorted next. The field in the first drop down menu has a higher priority over the second drop down menu. Priority meaning that your report will be sorted first by field selected in the first box, second by the second box, and so on.
Click on Next.

Now, at the next window, select the layout of your report. Select the layout you want for your report and click on Next.

The next window allows you to select the style of your report. The style will enhance the appearance of the text inside your report. Click on Next after selecting a style of your choice.

Finally, in the last window, give a name to your report. After typing in a new title, click on Finish.

Exercise:

Create a new report from Query 1, containing the fields: Name, Student Number, and Class Name.

The purpose of this report is to show who is in what class.

Group the report by Class Name.

Editing Reports

To edit your report, click on your report then on the design icon in the Database window.

The text boxes in the header segments of your report and the text boxes in black are the actual data from your database. You can click and drag the text boxes to different locations and change the style of the text by clicking on the text box. To insert more fields into your report, go to the View menu and select Field List. Now click on the field name and drag it onto the report grid. To separate labels from text boxes, edit them the same way as you did in your form. Just click and drag the large black box in the corner of the label or text box.

Exercise:

Edit your report, by adding new labels.

Creating Groups and Summaries using the Wizard

Groups must be created within the Report Wizard. Follow the steps listed above in Defining a report . When you reach the step that asks if you want to set grouping priority select a field in which you want to group your records. Notice that the chosen field becomes bolded and in blue in the preview window. Click Next to proceed.

You will then be asked to sort or summarize your data. Choose a field and select whether to sort it Ascending or Descending. Then click on the Summary Options button.

These summary options allow you to calculate averages and calculate sums of only numerical fields. To create a Count summary you must create a Group Summary which is outlined in the next section. Complete the Report Wizard. Your report will now be grouped and should look something like this:

Exercise:

Create a report that includes grouping and summary statistics.

Creating Group Summaries in Design view

Group summaries are used to give subtotals or statistics for a certain group of data within a report.

Begin by opening up your report in design view. Then Right Click anywhere on the report grid; try to avoid clicking on fields or labels. You will open the following menu. Click on Sorting and Grouping.

The following window will open. Notice all the options. You can create more groups, by adding more sorting fields. To add group summaries we must add a group footer. You can do that by selecting which field you wish to display group statistics for and then change the group footer option. to Yes. Note that if you have made a field a group, "Group Header" is set to Yes or "Group Footer" is set to Yes.

You will now have a new section in your report. Now we have to add text boxes to this new section to create group summaries. To add text boxes, open up the toolbox. This is done by clicking on the following icon:

Then create a text box, by clicking on the text box icon. Within the report grid, click, hold, and drag out a new text box.

You will notice that the label seems to be underneath the field. To fix this click on the large black square on the top left hand corner of the label. The drag the label to an appealling position. You will end up will with something like this:

Now, right click on the text box and select properties from the menu; it's at the bottom.

If you are not already in the Data tab, click on it now. Within the Data tab, click on the three dots at the end of Control Source

The expression builder will open up. You can create expressions just like you did in Queries. But, to create summary statisics we will use these formulas: =Avg([table name]![field name]), =Sum([table name]![field name]), =Count([table name]![field name]).

To add the formula into the expression builder type: =count( Then in the bottom windows, either in the Tables or Queries folder select the field you want to find the count of. You can also add formulas for avg, sum, etc.

Note: The formulas are not case sensitive, so you can use lower case letters (Ex: avg) or upper case (Ex: Avg).

When you're done just click OK. You should end up with something like this:

To add summary statistics to the end of the report, open up the report footer section and then follow the same steps as you would to add a group statistic in design view.

You should get something like this:

To view your finished report, exit design mode by closing the design window. Now, double click on the report you wish to view.

Exercise:

Find the average Test Mark and the count of Class Name for each student number.

Find the count of every student number found in Query 1, use Report Summary Statistics