I have two columns, one is Grant Award, the other is Project Status.
The Project Status could be complete, active, or withdrawn. At the
bottom of the report, I want a field for Total Amount Committed. This
would need to be the Total Grant Awards, less the Grant Award if the
Project Status is Withdrawn. Withdrawn projects have had the funds
reallocated. Can you help?

Hey Cher,

Would it help if you had three Grant Award columns instead of just one--one
for each Project Status. Actually I suppose you could have four if you
wanted to keep the current one too.

Anyway, you could run totals for each of these columns in your report footer
and do whatever math you want using whichever total you want. If you don't
want to see the new columns in your report make them not visible.

Get your columns in the query with something like CompleteGrantAwardAmount:
IIF([Project Status]='complete', [Grant Award],0) for each of the Project
Status.

Mike

"Cher" <ctcdtm (AT) juno (DOT) com> wrote

If you can have multiple Awards for a particular Project, you should house
this data in a separate table and link it back to the Parent table. Housing
this data over multiple columns violates data normalization practices and
can lead to troubles. What happens when you add a fifth Award, or a sixth
.... you would have to add a new column each time you needed to. Instead,
with a one-to-many relationship between the Projects table and Awards table,
you can add as many Awards as necessary.
--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

"mharness" <mike_harness (AT) hotmail (DOT) com> wrote