Budget Calculations in Access

[arwa alharbi]

Hello, and thank you in advance for your time.

I'm using Ms Access 2007 to create a database that contains 5 different training course that we offer, each course receives a certain budget per year, each time the course is given throughout the year we calculate the cost as follows:

number of trainees * cost per trainee = Amount spent

then

Amount spent - main course budget = budget remaining

I made a table in access called "Course_Budget" that contains Course_type, Main_Assigned_Budget, Assigned_Date

and another table called "Course" which has all course information such as number_of_trainees, cost_per_trainee,location, partner, duration and such.

I created a query as follows

Course_type, Main_Assigned_Budget, Number_of_Trainees, cost_per_Trainee

with two calculated fields called "Amount_Spent" and "Budget_Remaining"

My problem is that the outcome is not accurate, because it deducts the Amount_Spent from Main_Assigned_Budget in every row, which gives different Budget_Remaining every time.

I just want a way to make it consistent in a way that when it deducts the amount for the first time, the database should know to deduct the new Amount_Spent from that last output.

I could upload my DB if my explanation wasn't enough.
and thanks again.

[nico5038]

Basically you need a "running sum" for the "Amount_Spent" and calculate the correct "Budget_Remaining".
This can be achieved in a query using a DSUM() function with a "WHERE" part totalling all rows with a unique ID "equal or lower" as the current row.

The easy way is however to use a report and set the property of the "Amount_Spent" field to "Running sum" and add a calculated field for calculating the remainder.

Getting the idea?

Nic;o)