Summing label calculations in multiple records

[kjclark]

I have a label calculation ([lblBP]) to show the difference between two fields. These calculations are performed for each record.

Now, how do I calculate the total of all of the calculations [lblBP] over all of the records to put in the group footer?

Any ideas would be much appreciated!

Thanks in advance!
Karla

 

[cghoga]

in the same section, create a text box and set its control source to:

=[lblBP]

set its running sum property to Over Group

set its visible property to No because this field is used to build the footer calculation and does not need to be seen by the user

in the footer, create a text box and set its record source to:

= [NameofTextBoxYouCreatedAbove]

hope this helps.

 

[dhookom]

Running sums shouldn't be required if your "difference between two fields" is something like FieldA - FieldB. All you need is a text box in the group and/or report footer with a control source of:
=Sum(FieldA-FieldB)

Duane
MS Access MVP

 

[kjclark]

Thanks so much for your help!

I tried adding the invisible text box, but I keep getting an error, like it can't read the [lblBP] value. Is this because [lblBP] is a calculated label instead of a text box?

I used a label calculation because I needed to show the difference in Base Price across multiple records i.e. (Base Price in Quarter 1) - (Base Price in Quarter 2) = [lblBP]. Should I maybe be trying to do this calculation in a text box instead?

Thanks again for your help!
Karla

 

[kjclark]

I changed my labels back to text boxes and tried what you guys suggested above, but the sum is not showing up in the footer.

The calculation for the text box (calculating the differences) is done in VB in the background, so I am wondering if this is why I can't catch the value to sum it up.

Any ideas?

Thanks in advance!
Karla

 

[dhookom]

What do you mean by a calculated label? Label have captions. If you are doing significant calcs in your report, then you will need to add a text box that can support a running sum. Then do as cghoga suggested.

Duane
MS Access MVP

 

[kjclark]

I changed my calculated field to a text box, added running sum Over group to the properties.

the grouping structure is as follows:
Corporate Account Header

Invoice Year Header

Quarter Header
- this is where the text box is calculated [txtBP]

Detail

Quarter Footer
- when adding the text box TotalBP = [txtBP](in the control source) here I get the same calculation as above

Invoice Year Footer
- when adding TotalBP = [txtBP]here it is just blank in the report

Corporate Account Footer
- when I add it here it is also blank

Is there something else I need to be doing to the properties to make it show up?

Thanks!
Karla

 

[dhookom]

Is the txtBP in the Quarter Header calculated correctly? Is it visible? Does in show running sums (increate from one quarter to another)?


Duane
MS Access MVP

 

[kjclark]

Yes - txtBP is calculating the differences correctly from one quarter to the next. It is visible. It shows the correct sums from one quarter to the next, but is does not show cumulates as you add quarters. i.e. if a quarter shows $0, then the sum shows $0 instead of the total from the previous quarter.

Thanks!
Karla

 

[dhookom]

Don't set the value of this with code. Set the value of another text box with your code ie: txtMyValue. Then set control source of the text box you are using the running sum on to:
=txtMyValue


Duane
MS Access MVP

 

[kjclark]

I set the value of [txtBP] with code, then created text box[ActualBP] and set the control to =[txtBP] and set RunningSum to "Over Group" in the Quarter Header.

Now, down in the Corporate Account Footer, where I am trying to get the totals, I created a text box named [TotalBP] and set the control to =[txtBP]and the running sum to "Over Group" - when I run this, I get $0 in the field. Same thing happens if I set it to =[ActualBP].

I also tried setting this text box to =Sum([txtBP]) and then also to =Sum([ActualBP]), but the report doesn't recognize either of them.

 

[dhookom]

Try set the Running SUm of ActualBP to over all.

Duane
MS Access MVP

 

[kjclark]

Still the same result.... no sums, just $0 in the totals box..

Thanks!
Karla

 

[dhookom]

Is txtBP showing the individual totals?
Is ActualBP showing a running sum?
maybe try set ActualBP to
=Val(txtBP)

Duane
MS Access MVP