Re: Totalling a field that lives on a report only

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.
--
Marsh
MVP [MS Access]


Gina K wrote:

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...)



"Marshall Barton" wrote:

Gina K wrote:

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?


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
.

Relevant Pages

  • Re: Totalling a field that lives on a report only
    ... 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, ... txtCalcPts comes from: ...
    (microsoft.public.access.reports)
  • Re: Totalling a field that lives on a report only
    ... the Running Sum property to Over All instead of Over Group. ... Maybe I'll have to give the query route another go. ... per school (my report is not grouped on the field School, nor can it be, ... txtCalcPts comes from: ...
    (microsoft.public.access.reports)
  • Re: Totalling a field that lives on a report only
    ... Maybe I'll have to give the query route another go. ... per school (my report is not grouped on the field School, nor can it be, ... txtCalcPts comes from: ...
    (microsoft.public.access.reports)
  • Re: Totalling a field that lives on a report only
    ... I have a report, rptMeet, here?s a sample record: ... txtCalcPts comes from: ... I want to sum the txtFinalPoints field grouped on School (school parameters, ...
    (microsoft.public.access.reports)
  • Re: DB2 query manager for i5
    ... to do just seniors at one high school. ... or 4 places in the query, I can do any grade from 09 thru 12. ... So I converted the SQL statement into a query manager query...and ...
    (comp.sys.ibm.as400.misc)