I'm wondering whether what you actually want is the average of the two values
in the controls on the form rather than the average of a set of values in the
form's underlying recordset. If so don't use the Avg function but a
ControlSource of:
=(Val([Grade1])+Val([Grade2]))/2
However, if Grade1 and Grade2 are in fact columns in a table then Mark (aka
Steve) is, despite Arno's strictures, actually right about the flawed design
of the table. It should be decomposed as he suggests, but as Grade will be a
set of unique values in the tblGrade table you can just have the one column
Grade as the primary key of that table, and a corresponding Grade foreign key
in the tblItemGrade table, with referential integrity and cascade updates
enforced in the relationship between the two tables. The ControlSource for
an unbound text box to show the average grade per 'item' in a form based on
the tblItem table would then be:
=DAvg("GradeValue", "tblItemGrade", "ItemID = " & [ItemID)
though a more likely scenario would be to have a form based on tblItem with
a continuous subform based on tblItemGrade embedded within it, linked to the
parent form on ItemID, in which case you'd have a text box in the subform's
footer with a ControlSource property of:
=Avg([GradeValue])
We don't really have enough information from you about what your database
represents in terms of the real world to say which way you should be going.
Ken Sheridan
Stafford, England
Kit said:
Hi Arvin, and thanks so very much -- I owe you one!!!
I have my controlsource set with the parameters for the avg, but it's giving
me both field values: i.e. say my "grade1" is 50 and my "grade2" is 60, the
value in "grade" comes out as 2530. How can I merge grade1 and grade2. I
have them as separate fields, but if you can suggest a combined field that
will give me one result, I sure would appreciate it.
Arvin Meyer said:
Since you can do this in a form or query (or even a report) there is no need
to store the result. In a query use an alias column:
Grade: AVG([Grade1]+[Grade2])
in a form or report, set the controlsource of the Grade textbox to:
=AVG([Grade1]+[Grade2])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Kit said:
Hi,
I received a reply that my question on calculating averages had been
answered, when I click on the link for the answer, it was blank. Please
respond by to my question.
Can this calculation of averages be done from a form?
Form Name is Benton
I have three fields
field one is "grade"
field two is "grade1"
field three is "grade2"
I want the average of "grade1" and "grade2" to show in the "grade" field.
Can this be done with a form only, if not is there a way I can create a
macro, each time I try to create a macro, I only get the pre-defined
actions.
Thanks for any help you can give me.
