Fields Calculations & Update table

F

Faio

I have created a database for Employees.

Part of the database is a table to keep a record of Employees absent with
the following fields.
PF,FromDate, ToDate, and Reason. Days field is a calculation resulted by
Subtracting FromDate from ToDate.
Reason field was designed to have only SICK, ABSENT and ANNUAL_LEAVE. An
example is given below.

SICK_Entry Table
=================

PF FromDate ToDate Reason Days
---- -------- ------ ------ --------------------
12001 1/1/2004 3/1/2004 Sick 2
12001 11/3/2004 13/3/2004 Sick 2
12001 15/4/2004 17/4/2004 Sick 2
12001 21/4/2004 25/4/2004 Sick 4
12001 5/5/2004 9/5/2004 Annual leave 4
12001 5/1/2004 25/1/2004 Annual leave 20

Each employee is given a fixed number of ASSIGNED_SICK and ANNUAL_LEAVE days
in a year and is on another table like

ABSENT_Balance Table
====================
PF, Assigned_Sick, Annual_Leave, Sick_Balance,
Annual_Leave_Balance
--- ------------- ------------ ------------ --------------------------------
-----------
12001, 30, 20


Question:
---------
I need to create a formulae or whatever is suggested that will automatically
update the Sick_Balance and Annual_Leave_Balance fields in the
ABSENT_Balance table. Can you help me on this.............
E.g:

PF, Assigned_Sick, Annual_Leave, Sick_Balance,
Annual_Leave_Balance
--- ------------- ------------ ------------ --------------------------------
-----------
12001, 30, 20
20 -4

Also, when another record is recorded for the same employee like

PF FromDate ToDate Reason Days
---- -------- ------ ------ ----
12001 3/6/2004 5/6/2004 Sick 2

Then the ABSENT_Balance table record for 12001 will be

PF, Assigned_Sick, Annual_Leave, Sick_Balance,
Annual_Leave_Balance
--- ------------- ------------ ------------ --------------------------------
-----------
12001 30 20
18 -4


Can someone help me on this.
 
Top