none
Calc average from a subform RRS feed

  • Question

  • Good afternoon, all-

    I've got a form which contains a subform, the subform based on a query (I simply did a drag/drop of the query into my main form).  This subform appears in datasheet format.

    One of the fields in my subform is 'score'.  I'd like to have a text box in my main form's header which calculates the average score for the records showing in the subform.  I'd also like to be able to have this text box recalculate anytime the user filters the subform by using the drop downs on the datasheet's header.

    Eg: my subform has a field for product, as well as score.  If there were 5 records, then my textbox in the main form header would show the average for the score field from the 5 records.  But, if the user were to use the drop down on the field header for product, and select a product that filtered the subform to show 3 records, then I'd like for the text box in the main forms header to show the average for only those 3 records.

    Is this possible, or am I dreaming? 
    Thanks!

    Wednesday, January 27, 2016 9:01 PM

Answers

  • Yes it's possible. First create a proper subform rather than sloppy drag-n-drop a query.

    Then in its footer, put a textbox with:
    =Avg(score)

    Name it for example txtAvgScore

    Then in the parent form, pull up that value, using a textbox with a ControlSource of:
    =mysubformcontrolname.Form.txtAvgScore

    It should automatically refresh if you change the filter.


    -Tom. Microsoft Access MVP

    • Marked as answer by Casey_M Thursday, January 28, 2016 3:11 AM
    Thursday, January 28, 2016 2:20 AM