vacation leave accrual DB (Access2003/sp2)

[alexpereira]

I have set up an employee table with a DateofHire field. I need to get the date of hire from the Employee table and calculate how many months each employee has worked, and based on the length of time use accrual rates for vacation time:
13 days if worked less than 1 year;
14.50 days if worked 1-4 years;
16 days if worked more than 4 -6 years;
19 days if working more than 6 years.
So there are 2 parts to the issue: determine months worked, and based on results apply accrual rate to each employee's vacation time.

[Jezza]

Hi Alex

Instead of having a really long iif statement, I wrote you a quick function:

Function HolidayTime(datStart, datEnd)

Dim datDate As Integer

datDate = DateDiff("d", datStart, datEnd)

If datDate < 365 Then
HolidayTime = 13

ElseIf datDate >= 365 And datDate <= 1460 Then
HolidayTime = 14.5

ElseIf datDate >= 1460 And datDate <= 2190 Then
HolidayTime = 16

Else

HolidayTime = 19

End If

End Function


I have not checked it fully for the dates but will get you started

[alexpereira]

Thanks for the function.
How do I get the DateOfHire from the Emp1 table field DateOfHire?
Do I create a variable that matches the DateOFHire field, or can I just compare the field DateOfHire content and today's date directly?

[Jezza]

Hi Alex

I created it to be datStart as your hire date and datEnd as the date today or now()

Have a look at the image below to see how i set up the query

[Jezza]

Actually, to make it easier to see what I have done have a look at the attached. I have just changed the now field to Now(). I realised too late as I started off with a dummy set of data in an excel workbook...enjoy

[alexpereira]

When I try to do this I get the following error..

[Jezza]

That tells me you have not put the function in a module have a look at my sample database in <post:=603,894>post 603,894</post:>

[alexpereira]

Thanks Jerry,
Got it..
Now, lets make this a little more "interesting": If I want to put the accrual rates in a table instead of directly on the argument or function, and have the argument run the accruals based on dates from the employee table, but getting the rates from an accrual rate table? If I do it this way, I do not have to fiddle with the argument anymore if and when they decide to change the rates. All I would need to do is change the rates at the accrual rates table.

[Jezza]

I am assuming you have a tblRates table. Now you could have this as a look up table to a combo field in your tblEmployees. There are other ways with Dlookups but I am geeting towards the end of a long evening here so may have to hand over the baton to another Lounger in another time zone...but that is a quick suggestion <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[johnhutchison]

Alex

I agree that it makes sense to put the rates into a table.
But what happens when the rates change. Do the new rates apply retrospectively? or just to time worked since the change occured?

And I am not clear what these rates mean. Do they represent the days accrued per year? So the actual amount of leave is calculated using a combination of these rates and time served. Even then someone who has worked more than a year is accruing at a rate of 14.5 , but they still only get 13 for their first year?

[HansV]

Here is a modified version of Jerry's database. I replaced the holidaytime function with a function Age that calculates the number of whole years between two dates.
I added a table tblRates, and used this in combination with the Age function to calculate the number of vacation days in the query.

[johnhutchison]

Ignoring the complications of my previous post I post an examples that puts the rates in a table, and uses a function fnGetRate(Years) to look up the right rate in the table.

[alexpereira]

When rates are changed, they would apply to time worked since the change occured.
The rates are days accrued per year, but I would break it out to a monthly equivalent. So accrual could be done monthly. If the rates for the employee did not change, then the sum of monthly accrual would be equal to the yearly accrual rate. However, if during the year the rates changed because the person has reached the next rate, the new rate would apply to the months after the rate increase.
If we were to break out the yearly rate into a monthly rate, at the time of an anniversary, the monthly equivalent would change from that time on.
Maybe thats where the datediff() months would be a better solution than a yearly diff.

[alexpereira]

Thanks Hans,
Now you had put the resulting acrual rate in a query.
What we have talked about was non-exempt employee accrual rates. Now I need to do the same thing for exempt employees. Do I just add a fileld in the employee table specifying whether they are exempt (yes/no) then check for age , then run a statement that would take the exempt status into account?
Also, I have created my tables as follows:
static Employee info (EMP1), .........Name/Address/EmployeeID..etc..
calculated Emp data(EMP2), ..........Vacation Accrual rate, Sick Accrual rate, Time of Employment
Transactional Emp data (EMP3), ...Date of transaction, Sick add (accrue) Vacation Add (accrue) Sick Subtract (used), Vacation Subtract (used)
Totals Data(EMP4)....Date of Balance, Sick Balance, Vacation Balance
All tables are related by Employee number..
What we have been discussing is data for EMP2, i.e.: accrual rates and time of employment.
How do I :
account for different accrual rates for exempt vs. non-exempt employees;
put the results from your query into the EMP2 table?
How would you run a monthy accrual?

[HansV]

I have attached a modified database that shows how to handle exempt/not exempt by adding an extra field to the tblRates table.
If you need to calculate the rate for say 5 months, you can multiply the yearly rate with 5/12.
You can use an update query to add the rates to a table.