Vacation Accrual - Challenge

[scifinut]

Our vacation accrual is very different...not only is it monthly, but it is
based on exact date hired (not just year of hire). For example, in 2009,
someone hired 3/15/03 will be in their 6th year of service and will start
accruing 3 weeks vacation on 3/15/09. Therefore they will accrue 6.67 hrs (2
week basis) for January and February, then March will be prorated to 8.20
((6.67/31*14 = 3.08) + (10/31*16 = 5.12)), then for the remainder of the
calendar year they will accrue 10 hrs (120/12)each month. The total accrued
in 2009 will be 111.54 hours. In 2010 they will accrue a straight 120 hours.
Is there anyway to calculate this in Access? Any help will be very much
appreciated.

 

[mscertified]

You will be calculating not in 'Access' but in Visual Basic - which is a
powerful programming language.
Of course, you can calculate anything provided you have the data stored and
available.
You will need the hire date and the accrual schedule. You can calculate the
elapsed time between dates using the datediff() function.
Look up the date functions in Help for the details.

-Dorian

 

[dch3]

You'll probably have to figure out how to translate the accrual parameters
into hard and fast values that can be placed into a table to actually
calculate the accrued values. In a simple model, you'd have a start date and
position with a field for annual number of weeks of vacation. As in...

Hire Date Position Annual Vacation
10/31/2001 Customer Service 3 weeks
12/03/2009 Customer Service 2 weeks

So that anyone hired between 10/31/2001 and 12/2/2009 would be given 3 weeks
of vacation. Keep in mind that this a VERY simple approach when you get to
accruals by hours you might need something like...

Hire Date Position Accrual Ratio
Minimum Accural Unit
10/31/2001 Customer Service .0125 1
12/03/2009 Customer Service .0083 1

Here Accrual Ratio is the number of FULL Days (8hrs) that are accrued for a
given time period. In the first record, I used 1/80 as in 1 day for each 80
hours. In the second record, I used 1/120 as in 1 day for each 120 hours. The
idea is to multiple the ratio by the total number of hours worked. The
minimum would be used so that only full days (or half) are given to the
person. That's the theory at least. Personally, we've just started the inital
discussion on a workforce management solution complete with all of this and
HR/payroll integration and personally, I"M glad i don't have to worry about
any of the numerous rules.