calculate totals in a form/subform

  • Thread starter Thread starter amy
  • Start date Start date
A

amy

I have a form that pulls information by social security
numbers. In the form is a subform that pulls data from a
different table. This subform has hours of training.

The table has numerous social security numbers...I need to
add all the hours corresponding to each ssn and put it in
a the form.


I am VERY new to access...please help?
 
Amy,

On the subform, show the form footer and add a textbox to it. In the
control source property of this textbox, type the following:

=Sum([Hours])

Where 'Hours' is the name of the field on the subform that contains the
number you wish to sum.

HTH,
Josh
 
Okay,
Here's your solution. You create a field in the
subform footer, say [TotalTrainingHours], set its control
source =Sum([TrainingHours]). It will sum the values of
the training hours fields in the subform.
Then, in the main form, say you create a field called
[TtlTrngHrs] . You set its control source equal to the
value of the summed field in the subform; the syntax is:

=[Forms]![MainFormName]![SubFormName].[Form]!
[TotalTrainingHours] .

What this does, is specify that the field [TtlTrngHrs] in
the main form, equals the value of the field
[TotalTrainingHours] in the subform, which was calculated
as the sum of the [TrainingHours] fields. The terminology
is to specify the object type [Forms], then the !
separates to a lower object, in this case the name of the
main form [MainFormName], again the ! separator to the
lower object subform [SubFormName] , then a period
followed by [Form] which tells Access that the lower
object to the main form is also a form, then the !
separator again, to the lower object which is the field in
the subform that contains the value you want, in this case
[TotalTrainingHours].

This will put it in the form (I often put these types of
things in footers). It does NOT save it to the table,
you would need either a macro called with the SetValue
function, or use pretty complex CalculateTotal actions in
Visual Basic.

Bernie Maroney

HTH (hope that helps)
 
Back
Top