Budget Calculations

[Arwa]

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.

[rpeare]

can you post your table structure?

If I were doing this I would have a table structure something like:

Courses
CourseID (autonumber)
CourseName
CourseCostPerClient

Budget
BudgetID (autonumber)
CourseID (number from courses table)
BudgetAmt (though it appears you're using different costs for the same course for different contracts)
BudgetYear (since I'm assuming you have an annual budget for each)

CourseHistory
HistoryID (autonumber)
CourseID (number from courses)
TraineeCount (number)
CourseDate (date/time if they are one day courses)

It doesn't appear to be important who your trainees are, you are just after a total count in your calculation. Next it appears from your query that you want the deductions to happen by COURSEID not by the client and you want a running sum where the amount remaining on the budget is the sum of all prior classes subtracted from the budget amount and you want that amount displayed on each row.

You can do this in a query but it's extremely clunk, particularly if you start to get a lot of data in these fields. If I'm right, and please correct me if I'm not you want your first two rows of your query to appear like this:

Cont Course Train CostCoure Budget Spent Remaining
SB1  Sm Bus 2     100       100000 200   99800
SB4  Sm Bus 2     200       100000 400   99400

if this is actually what you want does it have to be in a query or can you show it on a report and it will be acceptable?

[Arwa]

Hello rpeare!

Thanks for taking the time to answer my question, everything you have said is 100% correct, that is exactly how I want it to appear!

It doesn't appear to be important who your trainees are, you are just after a total count in your calculation. Next it appears from your query that you want the deductions to happen by COURSEID not by the client and you want a running sum where the amount remaining on the budget is the sum of all prior classes subtracted from the budget amount and you want that amount displayed on each row.

Regarding the report, yes its acceptable but I have no idea how to fit in the running sum in the report and that's where I need help, much appreciated!

[rpeare]

Here's an example of a running sum report.

My table assumes you can get all of your data into a query (I added an artificial date to create some sort of reliable sorting mechanism.

on the report you'll notice two additional fields

TOTALUSED
REMAINING BUDGET

You'll notice the TOTALUSED column record source is TRAININGCOST but if you go to the RUNNING SUM property you'll see it says OVER GROUP. This means all your trainingcosts will be summed over the group (I've set up sorting and group by contract type first, then by date. I've given this field in the report the name TOTALTRAINING. Then in the last field BUDGET REMAINING it's just the name of the budget field (BUDGET) minus the total training cost field name (TOTALTRAINING)

[Arwa]

I just applied that to my DB and it worked perfectly!
You have such a clear way of explaining things, I followed it and it was flawless,
Thank you so much for your help and time