Total in Access Reports

[Sophie642]

I am running a report that will display tunits for each member of our
association. There are around 250 records that will have numerous tunits for
different items.
I am trying to set up a total of tunits for each member in this report.
tunits: Sum(IIf([units]=999000,0,[units]))/100
and the total at the end of the report is
=Sum([tunits])
All I get on the report is #error
what am I doing wrong?

 

[Marshall Barton]

I am running a report that will display tunits for each member of our
association. There are around 250 records that will have numerous tunits for
different items.
I am trying to set up a total of tunits for each member in this report.
tunits: Sum(IIf([units]=999000,0,[units]))/100
and the total at the end of the report is
=Sum([tunits])

The aggregate function only operate on fields in the
report's record source, they are unaware of controls on a
form/report.

Use the same formula for the grand total that you use for
the group subtotal. I.e.

=Sum(IIf([units]=999000,0,[units]))/100

 

[Sophie642]

Hi Marshall,

Thank you for your help. I tried what you suggested by I get the same pop up
box that says (Enter Parameter Value) List_Sort.... I press okay and the
report pops up but with the #Error message in total box.

Any suggestions?

I am running a report that will display tunits for each member of our
association. There are around 250 records that will have numerous tunits for
different items.
I am trying to set up a total of tunits for each member in this report.
tunits: Sum(IIf([units]=999000,0,[units]))/100
and the total at the end of the report is
=Sum([tunits])

The aggregate function only operate on fields in the
report's record source, they are unaware of controls on a
form/report.

Use the same formula for the grand total that you use for
the group subtotal. I.e.

=Sum(IIf([units]=999000,0,[units]))/100

 

[Marshall Barton]

You are being prompted ro enter a value for "List_Sort"??
That is not event mentioned in the expression.

It sounds like the prompt is coming from somewhere else in
the report.

I don't see how you can get the correct subtotals and still
get an error in the report footer grand total text box (it
its in the Report Footer, right?)
--
Marsh
MVP [MS Access]

Thank you for your help. I tried what you suggested by I get the same pop up
box that says (Enter Parameter Value) List_Sort.... I press okay and the
report pops up but with the #Error message in total box.

I am running a report that will display tunits for each member of our
association. There are around 250 records that will have numerous tunits for
different items.
I am trying to set up a total of tunits for each member in this report.
tunits: Sum(IIf([units]=999000,0,[units]))/100
and the total at the end of the report is
=Sum([tunits])

The aggregate function only operate on fields in the
report's record source, they are unaware of controls on a
form/report.

Use the same formula for the grand total that you use for
the group subtotal. I.e.

=Sum(IIf([units]=999000,0,[units]))/100

 

[Sophie642]

The total function is in the page footer. I've tried putting it in the report
footer but it totals everyone's units instead of totaling just one
individual's units. Could I email you a screen shot?

You are being prompted ro enter a value for "List_Sort"??
That is not event mentioned in the expression.

It sounds like the prompt is coming from somewhere else in
the report.

I don't see how you can get the correct subtotals and still
get an error in the report footer grand total text box (it
its in the Report Footer, right?)
--
Marsh
MVP [MS Access]

Thank you for your help. I tried what you suggested by I get the same pop up
box that says (Enter Parameter Value) List_Sort.... I press okay and the
report pops up but with the #Error message in total box.

Sophie642 wrote:

I am running a report that will display tunits for each member of our
association. There are around 250 records that will have numerous tunits for
different items.
I am trying to set up a total of tunits for each member in this report.
tunits: Sum(IIf([units]=999000,0,[units]))/100
and the total at the end of the report is
=Sum([tunits])


The aggregate function only operate on fields in the
report's record source, they are unaware of controls on a
form/report.

Use the same formula for the grand total that you use for
the group subtotal. I.e.

=Sum(IIf([units]=999000,0,[units]))/100

 

[Duane Hookom]

If you want "totaling just one individual's units", place the total text box
in the individual group footer section.

--
Duane Hookom
Microsoft Access MVP

The total function is in the page footer. I've tried putting it in the report
footer but it totals everyone's units instead of totaling just one
individual's units. Could I email you a screen shot?

You are being prompted ro enter a value for "List_Sort"??
That is not event mentioned in the expression.

It sounds like the prompt is coming from somewhere else in
the report.

I don't see how you can get the correct subtotals and still
get an error in the report footer grand total text box (it
its in the Report Footer, right?)
--
Marsh
MVP [MS Access]

Thank you for your help. I tried what you suggested by I get the same pop up
box that says (Enter Parameter Value) List_Sort.... I press okay and the
report pops up but with the #Error message in total box.


:

Sophie642 wrote:

I am running a report that will display tunits for each member of our
association. There are around 250 records that will have numerous tunits for
different items.
I am trying to set up a total of tunits for each member in this report.
tunits: Sum(IIf([units]=999000,0,[units]))/100
and the total at the end of the report is
=Sum([tunits])


The aggregate function only operate on fields in the
report's record source, they are unaware of controls on a
form/report.

Use the same formula for the grand total that you use for
the group subtotal. I.e.

=Sum(IIf([units]=999000,0,[units]))/100

 

[Marshall Barton]

The total function is in the page footer. I've tried putting it in the report
footer but it totals everyone's units instead of totaling just one
individual's units.

All this confusion is because you said you want the total
"at the end of the report". Page footers normally appear on
every page. The Report footer only appears after all the
other stuff in a report.

Since pages are not a data related object, there is no way
to calculate an aggregate value in a page header/footer
section. As Duane said, you need to put the subtotal text
box in the person GROUP header or footer section.

If you only want to see the subtotal in the page footer and
you can guarantee that there is only one person group per
page, then you can use a page footer text box that
references the person group calculated text box.

A different way to calculate the person subtotal is to use a
RunningSum text box in the detail section and refereence it
in a page footer text box.