how to subtract from field in table

[ghost]

Greeting,

I have a table for employees and one for Departments. Employee table has
employee ID, Name and salary. Departments table has Department ID, Name, and
Balance. What I want to do is once a new or current employee takes or
recorded in employees table, the salary will be subtracted from Balance field
accordingly. The result will be stored for new inputs and showing for the
user to alert the registration that if no more balance available. How can I
do that?

 

[Bob Quintal]

Greeting,

I have a table for employees and one for Departments. Employee
table has employee ID, Name and salary. Departments table has
Department ID, Name, and Balance. What I want to do is once a new
or current employee takes or recorded in employees table, the
salary will be subtracted from Balance field accordingly. The
result will be stored for new inputs and showing for the user to
alert the registration that if no more balance available. How can
I do that?

Your problem can be solved easily by replacing the BALANCE field
with the BUGET value for thes salaries, then your BALANCE simply
becomes a calculated field in queries, forms and reports,
You'll also need the Department ID in your Employees table, so you
know what department the employee works in and which department's
budget to subtract from.

BALANCE: BUDGET-DSum("salary","EMPLOYEES",
"[DEPARTMENT ID]= " & DepartMent ID)

(all on one line in a query.)

 

[Tom van Stiphout]

On Sun, 24 Feb 2008 08:56:03 -0800, ghost

Balance is a calculated field and thus does not belong in a table.
Rather you should create a query that returns Balance.
In a simplified scenario Balance = Budget - Sum(Salaries)

So assuming your Department table has a Budget field:
SELECT tblDepartments.DeptID, tblDepartments.DeptName,
tblDepartments.Budget, Sum(tblEmployees.Salary) AS SumOfSalary,
[Budget]-Sum([Salary]) AS Balance
FROM tblDepartments INNER JOIN tblEmployees ON tblDepartments.DeptID =
tblEmployees.DeptID
GROUP BY tblDepartments.DeptID, tblDepartments.DeptName,
tblDepartments.Budget;

Of course this does not take into account complications such as
monthly payout of salary, salary adjustments, and annual adjustment of
budget, etc.

-Tom.