I have put together a Human Resource database for your company. The HR manager wants me to setup the vacation time field to automatically adjuster it's self each time their vacation changes each year from their Hire Date. My fields are: Date_of_Hire and VTBeginningBalance. Each time their hire date comes around I need it to calculate and fill in the VTBeginningBalance field.
0-1 year no vacation on their 1st anniversary date they get 40 hours.
On their 2nd anniversary they get 80 hours.
And it doesn't change until they reach their 5th year anniversary. On their 5th anniversary they get 120 hours. I need help with the access code and also where to place the code. Do I place it in an event procedure under Date_of_Hire or VTBeginningBalance? I have been trying everything.

Can someone please help me out on this one.

Thank you!

Hi, Saundra,

Keeping vacation time with every employee's record does not sound a good idea, because it depends on the time an employee has been
with a company rather then on a particular employee record.
In your example, there will be a group of employees, who has worked for less than 1 year, a group of people who has worked
for 1-2 years, 2+ years and the rest with 5+ years service history.
Besides, if you keep vacation time with the employee record,
WHEN are you going to update the value?
You would not like to have a program running all the time for years
in order to catch the moment. Doing this at the point when you
actually want to check the values will do so every time you open
an employee's record (let's say), which does not make sense either.

I would suggest creating a separate table, which holds correct vacation time for every period, you described. Something like:

Period Vacation time
1 40
2 80
5 120

Then every time you open the employee record, your program will calculate the difference (in years) between the current date and the employee's start date and then return vacation time from the above table. I would do this on "greater then" basis starting from the maximum period.

I hope this helps.




So, your VTBeginningBalance will be a dynamically calculated value
rather then a physical database field.