Sum Calculated Datasheet Column (1 Viewer)

[wem6097]

I have been dabbling in Access 2007 for a couple of years and need some advice on form handling.

I have a form that is based on a parent table and a subform datasheet based on a related child table. The purpose of the form is to post a general transaction and detailed purchases in the child subform on a datasheet. Both the main form and the subform are tables in my database. I have added a column to the subform datasheet to calculate the (units x price). This is solely for the purpose of showing the extended amounts for each detail. I am not trying to permanently store the resulting values in the details table. I would like to be able to total the calculated column, but the only choices I am offered are COUNT and NONE. I do not get a choice allowing me to SUM the column. Can anyone advise me on getting a SUM total for this column?

 

[RainLover]

Place this in the Control Source of a new Text Box on the form.

=Sum([NameOfField]) where NameOfField is the name of a field in the query. (Not The Form)

 

[Pat Hartman]

The new features in A2007 wll not allow you to aggregate (sum, avg, etc) the value of a calculated control and if you think about it, it makes sense. Access would need to calculate the values for an entire set of records and store that somewhere. Access only calculates values for visible rows so there is no way for it to come up with a sum that goes beyond that scope. However, you can do this yourself. Add a footer to the subform, put the calculated control there. Remember, forms and reports can't aggregate unbound controls either so you need to repeat the calculation.

=Sum(Nz(fldA,0) * Nz(fldB,0))

 

[wem6097]

Many thanks for all your replies.

Regards,

Wayne (wem6097)