Having trouble summing a calculated field (1 Viewer)

[connieharper]

I have a field (text box) on a report named [Gradetotal], the source property for the field =(Avg([GradeGrade])*[weight]/100*100). The data on the report is grouped by Student and ClassID. After each CLASSID group, the total is printed for that group in the [Gradetotal] field. This all works fine.

I have created another text box called [FinalTotal] and I want it to be the Sum of the [GradeTotal] field. It will print at the very end of the report. So, on the source for the [FinalTotal] field I put (sum[Gradetotal]) but when I run the report I get a message stating the field [GradeTotal] does not exist, when in fact it does. It is not a field in the Query but rather a calculated field on the report. Is this not the proper way to get the sum of the [GradeTotal] field?

 

[barrygraham]

I think, if I have understood you correctly, what you need to do is this:

In the group footer, you will need to set the control scource of a text box to this:

=Sum(Avg([GradeGrade])*[weight]/100*100)

You will then get the overall sum of your field.

I hope this is what you needed and my solution helps you.

Barry.

 

[connieharper]

Thanks for your quick response. That solution seems logical but when I try it I get the following message. "Can't have aggregate function in expression".

 

[barrygraham]

Try working out this in your footer:

=Avg([GradeGrade]) call it "overall average"

then work out the total weight

=Sum([weight]) call it "total weight"

Then in another texbox try this:

=Sum([overall average]*[total weight]/100*100)

I think that may give you what you are after, but if it doesnt, I am sorry but it has been a very long day. Maybe I will be able to think better tomorrow.

Barry.

 

[Richie]

This article should help you Article ID: Q129096