Totalling a field that lives on a report only

[Guest]

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?

 

[Marshall Barton]

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

 

[Guest]

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…

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

 

[Marshall Barton]

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]

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…

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

 

[Guest]

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

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

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

 

[Marshall Barton]

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

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



:

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

 

[Guest]

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
the Running Sum property to Over All instead of Over Group.
--
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
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.
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.


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



:

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

 

[Cydney]

I'm doing this same thing and it works to have the total in the group footer.
However, because of my report layout, I need the total to appear in the group
header instead. Right now it's only giving me "0" as the referenced value.
But referencing the value in the group footer gives me the accurate sum
total. How can I make it appear in the header?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

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


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



:

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