Subforms (2)

Display “Subform” calulations in a form.
In “Access”, we can perform calculations in a “Subform”, and display the result in the “Main form”.
In the example below, I have the “Main form” that displays the student and a “Subform” that displays the results of tests the student has taken.

So you have the ability to calculate the average (or any other calculation) of all tests in the “Subform” and display it in the “Main form”.

Proceed as follows:
First select the “Design View” in the “Ribbon”.
In the ‘Results’ “Subform”, add a text box in the footer section.

You give the label as “Average”.
And for the box itself, you give an appropriate name, such as “avgscore” in the “Properties window” under the “Other” tab.

We need to refer to this text box, so it is easier if we know what this text box is called.
Then type the “Formula” in the text box as = Avg ([score]).
I will try to clarify what this “Formula” does:
Every “Formula” has to start with the equals sign.
Then type Avg, for average.
Then open the parentheses.
Type in the range between square brackets , for which you want to carry out the calculation, in this case, this is the score field.
Close the parentheses.

Why type all this in the footer section of the “Subform”?
Well if you do this in the “Details” section, this is also added next to each record in the “Subform” – but this is not what I want.

Ok, the first part is done.

Since the “Subform” is displayed in “Datasheet view”, this “Formula” is not visible.
So we will have a text box in the “Main form” that refers to this “Formula” in the “Subform”.

Proceed as follows:
First select the “Main form”.
Insert a text box in the “Main form” and give it the label as “Average Score”.


Select the text box and click the “Property Sheet” button in the “Ribbon”.

Under the “Data” tab, click on the button to the far right of “Control Source”.

This opens the “Expression Builder” dialog box:

In this dialog box, select the text box that you just created in the “Subform”.
This can be found if you double-click on the name of the “Main form” in which you work. This, in this case, is the “Students” (1) form.
Under this form, you see the “Results” “Subform” so, select it (2).
In the middle of the dialog box, double-click the newly created text field called “avg score” (3).
(And here again is a good example to tell you that you have to create the controls with an appropriate name.)

And click the OK button (4).
And save the changes.
Go to “Form view” and see the result.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats