SUM Functions in Subforms

  • Thread starter Thread starter Benjamin Piercy
  • Start date Start date
B

Benjamin Piercy

Dear all,

I have a Student form with two subforms. One subform
contains students subject grades and the other one
contains students univeristy offers. Both subforms have
IF functions which successfully convert grades to points.
The subjects subform adds up the points successfully via
a text box located on the form itself containing the
expression =[QryGradestoPoints subform4].Form!footer.
The textbox which this refers to in the subform has the
expression =Sum([points]). It successfully adds points up
and gives a total points.

E.g. Economics A 120 points total points 340
Maths A 120 points
English B 100 points

HOWEVER, when i come to doing this in the offers subform
it is considerably harder. The reason behind this is that
more than one university can make an offer, therefore the
subform looks like this:

Cambridge A B B 120 100 100
Oxford A B B 120 100 100
Leeds B B B 100 100 100 total points 940


=SUM([Offer1+Offer2+Offer3]) NOTE, this is the only
function for addition i know of....

As shown above rather than adding each row up it adds up
ALL Offers score. Note- there are 3 columns: Offer grade
1, Offer grade 2, Offer grade 3 and then three more
columns which display the points for each grade.

I need to be able to display each univeristy's total
points required as oppose to a total for all the
unverisities together.

E.g. Cambridge A B B 120 100 100 Total points 320
Oxford A B B 120 100 100 Total points 320

I then have to somehow compare the points obtained with
all the required points from each university to be shown
in a report. I dont know how i'll tackle doing that
either, but thats something i suppose i can address
later......

Any help would be greatly appreciated!

Best Regards,

Ben Piercy
 
in the offers subform, move the calculated control out of the form footer
and into the detail section, where it will display a value for each record.
remove the sum function, so the formula is more like
=[Offer grade 1] + [Offer grade 2] + [Offer grade 3] (and the rest of the
fields you want to total)
this solution assumes that the data you posted, in rows, represents actual
rows (records) in your underlying table.
suggest you save "required points from each university" in a field in the
universities table. you can calculate total required points per university
in a report, same as in the form, and create a calculated field to compare
the two numbers - as a percentage, or however you want.

hth
 
Back
Top