Edit a value automatically

[Anand Vaidya]

I have a table tblLeaves_Available with fields
FK_Emp_Id
Total_Leaves

I want to add one leave to each of the employee every month
automatically.How can I do this?Suppose an employee has 3 leaves available
(Total_Leaves)this month,it should increment by 1 the next month
automatically.

 

[John Vinson]

I have a table tblLeaves_Available with fields
FK_Emp_Id
Total_Leaves

I want to add one leave to each of the employee every month
automatically.How can I do this?Suppose an employee has 3 leaves available
(Total_Leaves)this month,it should increment by 1 the next month
automatically.

You shouldn't store this calculated value in the table AT ALL.
Instead, calculate it dynamically. You can use Month(Date()) in a
calculation to get the current month; if the user gets one day of
leave per month, you could use an expression like

Total_Leaves: Month(Date())

Available_Leaves: Month(Date()) - Leaves_Taken

in two vacant Field cells in a query.

John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

I don't think it's quite that simple John. Not everyone will have been
employed since January and the expression doesn't handle leave carried
forward from previous years.
My first inclination is to create individual leave accrual records. These
can be summed with the leave taken records to obtain the current balance.
This is how many inventory applications work. But I'm thinking that maybe
this is a case for a combination of methods. Perhaps, leave carried forward
from previous years can be stored in the employee's record to avoid having
to deal with records from previous years. This also takes care of any
conversion issue. And then the three sources of leave info would be
summed - carried forward + accrual records + taken records.

 

[John Vinson]

I don't think it's quite that simple John. Not everyone will have been
employed since January and the expression doesn't handle leave carried
forward from previous years.

Good point, Pat. I guess it depends on the business rules. If the
accumulated leave can be readily calculated from data that already
exists (such as today's date and other data in the table) then it
should be calculated; if - as you suggest - it depends on other
factors which would make an expression unwieldy or impossible, then it
should be stored.

John W. Vinson[MVP]