Subform Calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a continuous datasheet subform that I want to sum the rows
individually and then have a grand total of all data in one form's subform.
Can this be done?
 
Add a footer to the subform. Add a control to the footer that contains the
sum() function and give the control a meaningful name:
=Sum(someAmtField)
If your subform is in continuous view, you can arrange the window so that
the subform footer shows. This won't work for a datasheet form though since
they don't display headers or footers. In that case add a control to the
main form and for its ControlSource, use:
=subformcontrolname.meaningfulsumName
 
Hi Natalie

Yes it can be done by adding an unbound field to the "footer" of the form.
=SUM([fieldname])

But - looking your post it may be that you're thinking a little bit along
the excel lines and not access.
 
Hi Wayne,
Yes, I have to think along "the excel line" because the people in my company
are reluctant to change and heaven forbid they have to input data entry into
a form that doesn't resemble a spreadsheet. I did manage to work something
out, thank you:)

Wayne-I-M said:
Hi Natalie

Yes it can be done by adding an unbound field to the "footer" of the form.
=SUM([fieldname])

But - looking your post it may be that you're thinking a little bit along
the excel lines and not access.
--
Wayne
Manchester, England.



natalie said:
I have a continuous datasheet subform that I want to sum the rows
individually and then have a grand total of all data in one form's subform.
Can this be done?
Click to expand...
Click to expand...
 
Good advice! Thank you, Pat!

Pat Hartman(MVP) said:
Add a footer to the subform. Add a control to the footer that contains the
sum() function and give the control a meaningful name:
=Sum(someAmtField)
If your subform is in continuous view, you can arrange the window so that
the subform footer shows. This won't work for a datasheet form though since
they don't display headers or footers. In that case add a control to the
main form and for its ControlSource, use:
=subformcontrolname.meaningfulsumName
Click to expand...
 
Hi again,
Still need help. I got the subform in to a continuous form and the calc box
that sums each line (record of subform) works great. What I can't get to
work is the field in the footer that sums all the records. I tried both
=sum([Line Sum]) and =Nz(Sum([Line Sum])) but I get #Error messages with
both. Any ideas?

Wayne-I-M said:
Hi Natalie

Yes it can be done by adding an unbound field to the "footer" of the form.
=SUM([fieldname])

But - looking your post it may be that you're thinking a little bit along
the excel lines and not access.
--
Wayne
Manchester, England.



natalie said:
I have a continuous datasheet subform that I want to sum the rows
individually and then have a grand total of all data in one form's subform.
Can this be done?
Click to expand...
Click to expand...
 
Hi again,
Still need help. I got the subform in to a continuous form and the calc box
that sums each line (record of subform) works great. What I can't get to
work is the field in the footer that sums all the records. I tried both
=sum([Line Sum]) and =Nz(Sum([Line Sum])) but I get #Error messages with
both. Any ideas?
 
You can't sum a control, you need to repeat the calculation in the Sum().
For example,

=Sum(fldA + fldB)


natalie said:
Hi again,
Still need help. I got the subform in to a continuous form and the calc
box
that sums each line (record of subform) works great. What I can't get to
work is the field in the footer that sums all the records. I tried both
=sum([Line Sum]) and =Nz(Sum([Line Sum])) but I get #Error messages with
both. Any ideas?

Wayne-I-M said:
Hi Natalie

Yes it can be done by adding an unbound field to the "footer" of the
form.
=SUM([fieldname])

But - looking your post it may be that you're thinking a little bit along
the excel lines and not access.
--
Wayne
Manchester, England.



natalie said:
I have a continuous datasheet subform that I want to sum the rows
individually and then have a grand total of all data in one form's
subform.
Can this be done?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top