Totalling a field that lives on a report only

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gina K - 29 Jun 2006 18:36 GMT

I just can’t seem to get my report to total correctly, perhaps I can’t even
do what I’m trying…

I have a report, rptMeet, here’s a sample record:
Place Athlete School Result FinalPoints
1 Jones JFK 12345 5

txtFinalPoints is calculated:
IIf(IsNull([OverridePts]),[txtCalcPts],[OverridePts])
OverridePts is entered in the table on a form and resides in the report’s
record source, qryMeetRpt
txtCalcPts comes from:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If ([Relay] = False) Then
Select Case txtPlace
Case 1
txtCalcPts = 5
Case 2
txtCalcPts = 3
Case 3
txtCalcPts = 1
Case Else
txtCalcPts = 0
End Select
Else
Select Case txtPlace
Case 1
txtCalcPts = 5
Case Else
txtCalcPts = 0
End Select
End If

I want to sum the txtFinalPoints field grouped on School (school parameters,
txtSchool1 and txtSchool2, are supplied by a form, frmPrintRepts).

Can someone point me in the right direction so that I can sum for School1
and School2 in the group footer section, even though the field I’m summing is
calculated only on this report, and doesn’t live anywhere else in my database?

Reply to this Message

Marshall Barton - 29 Jun 2006 19:02 GMT

>I just cant seem to get my report to total correctly, perhaps I cant even
>do what Im trying
[quoted text clipped - 35 lines]
>and School2 in the group footer section, even though the field Im summing is
>calculated only on this report, and doesnt live anywhere else in my database?

Add another text box named txtRunPoints next to the final
points text box. Set its control source expression to
=FinalPoints (use the actual name of the final points text
box) and set its RunningSum property ot Over Group. Then
the group footer can display the total in a text box with
the expression =txtRunPoints

Signature

Marsh
MVP [MS Access]

Reply to this Message

Gina K - 29 Jun 2006 21:24 GMT

The sum works over the entire group, but I still can't seem to get a total
per school (my report is not grouped on the field School, nor can it be,
because of the way I'm assigning the place and points value (a running sum
over a group)). I tried to assign the place value in a ranking query, but
that didn't allow me to assign places per group, only places per the entire
record set.

Any other suggestions? (As if you couldn't tell, I'm not very good at
this...)

> >I just can’t seem to get my report to total correctly, perhaps I can’t even
> >do what I’m trying…
[quoted text clipped - 42 lines]
> the group footer can display the total in a text box with
> the expression =txtRunPoints

Reply to this Message

Marshall Barton - 30 Jun 2006 00:48 GMT

I missed the part about the total per school. This is very
difficult to do without having the calculation in a query.
For just two schools, identified on your form, you can do it
with two running sum text boxes named txtRunSchool1 and
txtRunSchool2. The expressions would be:

=IIf(School=Forms!frmPrintRepts.txtSchool1, FinalPoints, 0)

This will not generalize to an arbitrary number of schools.
The general approach is to calculate everything in a query
that can be used in a subreport, but it appears you are
headed down a different road.

Signature

Marsh
MVP [MS Access]

>The sum works over the entire group, but I still can't seem to get a total
>per school (my report is not grouped on the field School, nor can it be,
[quoted text clipped - 52 lines]
>> the group footer can display the total in a text box with
>> the expression =txtRunPoints

Reply to this Message

Gina K - 30 Jun 2006 01:39 GMT

Thanks so much...all I'll ever need to compare is 2 schools so that works
well. I guess, given what you said, that there's no way to get totals in the
report footer...
I wish I could calculate everything in a query, it would make things so much
simpler. Maybe I'll have to give the query route another go.
Thanks for your help.

> I missed the part about the total per school. This is very
> difficult to do without having the calculation in a query.
[quoted text clipped - 65 lines]
> >> the group footer can display the total in a text box with
> >> the expression =txtRunPoints

Reply to this Message

Marshall Barton - 30 Jun 2006 06:57 GMT

You can get the grand totals in the footer by using more
running sum text boxes with the same expressions, but set
the Running Sum property to Over All instead of Over Group.

Signature

Marsh
MVP [MS Access]

>Thanks so much...all I'll ever need to compare is 2 schools so that works
>well. I guess, given what you said, that there's no way to get totals in the
[quoted text clipped - 72 lines]
>> >> the group footer can display the total in a text box with
>> >> the expression =txtRunPoints

Reply to this Message

Gina K - 30 Jun 2006 14:46 GMT

Thanks a million for sticking with this thread! I finally got the results I
was looking for thanks to your help.

> You can get the grand totals in the footer by using more
> running sum text boxes with the same expressions, but set
[quoted text clipped - 75 lines]
> >> >> the group footer can display the total in a text box with
> >> >> the expression =txtRunPoints

Reply to this Message