Calculating Fields

[AppzDev]

I am new to this board and I hope i am posting this in the right forum, if not, my apologies. I have used this forum to help me create solutions already and find it a great resource for a rather new DBA.

I am a rather new DBA and am working on a database to replace an old Tuition Reimbursement database.

Here is my dilemma. I based my form from a query in order to calculate 3 fields. The calculated fields are "CurrentBalance", "OrgPaid", and "TotalCost" The calculations are as follows.

CurrentBalance: [AllowedBalance]- [OrgPaid]
OrgPaid: [FTE]*[TotalCost] (FTE is a constant integer or decimal)
TotalCost: [BookCost]+[TuitionCost]

AllowedBalance is a constant number.

Life is good if the user enters only ONE course. All of the fields in the query calculate just like i want them too and display on the form.

Life is bad when the user enters ANOTHER course. What i need this database to do is to have a "running total" of sorts of CurrentBalance. So, if TotalCost for the first course is 500 dollars and FTE=1, they employee has, let's say 2500 dollars left to spend for that year, being that the "AllowedBalance" is 3000 dollars. The second course is entered and the TotalCost is 500 dollars for that course, the employee now has a balance of 2000 dollars.

I am drawing a blank as to how i need to go about having a "running balance" throughout the year.

My initial thought is to have one EmployeeInfo table and an EmployeeBalance table linked by EmpID but i can't get a "running balance" of [AllowedBalance]-[TotalCost] to work when multiple records are entered for the same employee.

Any help is greatly appreciated.
Thank You,

DC~

 

[Kozia]

You need to group the records by employee. Create a query select employeeID and expr where you put the two fields to subtract. Click on Totals and under employeeID select GroupBy and under expr select Sum. It will give you the total per employee. Now that is the easiest approach. Or you can write a code to do it and display totals on screen.