What is the best way to set up Access for budgeting?

[migv1]

Greetings:

I've been assigned to set up a database that tracks budget data for multiple projects. What is the best way to automatically update a 'remaining funds' field for specific projects as new items are added to the database?

Thanks in advance for any and all suggestions!

 

[Golom]

Without sounding like a smart a** ... the best way is ... don't do it. "Remaining Funds" is a computed quantity consisting of "Allocated Budget" - "Money Spent". If you store the results of that computation then you will have to remember to update it every time the budget changes and/or money is spent.

Unless you have many thousands of items that you would need to add to do the computation, my preference would be to define a query that does the computation and displays the result on the fly. That gets you out of trying to keep base data and computed results in sync.

 

[migv1]

Sorry if my question wasn't clearly stated - what I really wanted to know was how do I make sure that the correct value of "Remaining Funds" (the current value would of course replace either the original "Allocated Budget" or the previous "Remaining Funds") is used when a new item is entered. Should this be tracked using a separate query?

 

[lespaul]

Like Golom said, you don't store calculated fields in the table. It breaks third normal form (and if you don't know what 3NF is, I would read up on relational databases before going any further).

You have a BudgetTable. For each project (or whatever) you store it's budget there. Then you have expenses for that project. If you store the "Remaining Funds" any time a new expense is added you have to update remaining funds. If an expense is removed you have to update remaining funds. If, however, you calculate 'remaining funds' in your query when you need it on the fly, it's always RIGHT! No recalculating or storing required.



Leslie