Form selection subtracted from other table.

[Remington]

I have a form named "Employee Absence". This form is linked to a table that lists each absence. (This table is differnet from the one mentioned below) In the "Employee Absence" form there is a drop down box for selecting the type of absence an employee used. The options are Sick Leave, Unpaid, Vacation and a few others.

In another Table called "Employee Information", I have the Employee's name, hire date, and some other personal records, as well as fields to store the ammount of Sick Leave, Vacation....ect ... The Idea here is to store the number of earned Vacation Hours, Sick Hours...ect... as they are accumulated.
(These numbers are entered manually into this table)

Example) Employee # 225 has earned 20 sick hours so far. This is stored in the "Employee Information table", under sick hours.

What I would like to do, is when you are in the "Employee Absence" form, and you enter a new record of Employee # 225 using up 8 hours of sick leave, then those 8 hours are subtracted from the "Employee Information" Table, Employee #225's sick hours. Thus the 20 stored hours, minus the 8 Hours.

So, idealy, the "hours gone" selected on my form, will be subtracted from the field in my "Employee Information" table. This is based on what type of absence is selected in the form. If sick leave is selected on the form, then I want the hours of sick leave subtracted from the saved "sick days" in my Employee info table.

Hope thats clear.....thank you for any input.

[MMcCarthy]

You shouldn't store calculated fields in a table as they are too difficult to keep up to date. Calculated fields should be stored dynamically on forms and reports as required.