Calculated filed in report

[gary g]

I have a calculated field in a report that keeps a running dollar total of invoices. The field name in the table is InvoiceAmount and the calculation is =Sum([InvoiceAmount]). Everytime a new invoice amount is entered in a form it automatically updates the report with just the new total dollar figure (i.e. the current total dollar amount of all invoices).

However, I also want to create a second calculated field on the report that will give me the same running total figure as above, but then subtract this total figure from a fixed dollar amount. For example, the FY2010 budget for this particular category is $13,000.00. How can I create a calculated field that will subtract the running total from this fixed amount every time an invoice amount is entered?

The purpose of the calculated field would be to automatically indicate how much is remaining in our budget everytime a new invoice amount is entered into the database (i.e. $13,000.00 minus the sum of all invoices=the result).

I am sure this calculation is possible, but I am unsure how to write the calculation itself. Can anyone help? Thanks in advance.

Gary
HOuston, TX

 

[Scooterbug]

Writing the calculation is easy, but there are several ways you can get the budget amount. One way is to hard code it into the calculation. The downside to that is if it changes (due to an increase in budget or a new budget year) you will have to go in and manually change it.

The other way is to store the value in a table. Doing it this way, if you need to change it you can simply change the value in the table. You can also input a new budget amount with each year. You would need to come up with a way to determine which budget amount you would use on the report.

The calculation would be (Off the top of my head):
=dlookup("[budgetAmt]","tblBudget","[budgetID]=Criteria") - sum([InvoiceAmount])