Conditional Totals in a Report

[Gary J. Jahnke]

In my database, I have multiple individuals who may complete projects for me.
There is an $800 cap per project - in other words, $800 is the most they can
receive. They submit itemized charges for the services they provide, and
sometimes these add up to more than $800. Is it possible to have a report
automatically solve this, like such:

Project #1
-- Service 1 $200
-- Service 2 $400
-- Service 3 $400
---- TOTAL FOR PROJECT #1: $800 (even though it's higher, the total should
display as $800, perhaps with a message to the effect of "you reached the
cap")

Project #2
-- Service #1 $150
-- Service #2 $ 75
-- Service #3 $ 50
---- TOTAL FOR PROJECT #2: $275

TOTAL FOR THIS PERSON: $1,075

I can get the project totals on the report with an IIF condition on that
field, and I can account for the "you reached the max" message the same way.
But I can't figure a way to make it take that into account when doing the
grand total for that person. Any suggestions?

 

[Marshall Barton]

In my database, I have multiple individuals who may complete projects for me.
There is an $800 cap per project - in other words, $800 is the most they can
receive. They submit itemized charges for the services they provide, and
sometimes these add up to more than $800. Is it possible to have a report
automatically solve this, like such:

Project #1
-- Service 1 $200
-- Service 2 $400
-- Service 3 $400
---- TOTAL FOR PROJECT #1: $800 (even though it's higher, the total should
display as $800, perhaps with a message to the effect of "you reached the
cap")

Project #2
-- Service #1 $150
-- Service #2 $ 75
-- Service #3 $ 50
---- TOTAL FOR PROJECT #2: $275

TOTAL FOR THIS PERSON: $1,075

I can get the project totals on the report with an IIF condition on that
field, and I can account for the "you reached the max" message the same way.
But I can't figure a way to make it take that into account when doing the
grand total for that person.

Add a hidden text box (named txtRunPerson) next to the
project total text box. Set its control source expression
to =[the project text box] and set its RunningSum property
to Over All (or Over Group). Then a text box in the report
footer (or the person group footer) can display the total by
using the expression =txtRunPerson

 

[Gary J. Jahnke]

Thanks, Marshall! It works perfectly!!!