Calculating anniversary dates for paid time off accrual starting on January 1st.

[ladyrider696]

I have an employee form that has is a field called PTO (Paid Time Off) Accrued. I need that field to populate automatically depending on the employees years of service using the tblPTODESC table (PTOCEILING field):

New hires Length of Service:
New hires who start on or before June 30th: 40 hours
1/1 following hire date - five years: 104 hours
1/1 following 5th year anniversary - ten years: 144 hours
1/1 following 10 year anniversary and after: 184 hours
Employees hired on or after July 1st are ineligible for PTO until January 1st in the following calendar year.

Grandfathered Length of Service:


Hired 1/1/93 - 7/31/12
1/1 following date of hire - five years: 120 hours
1/1 following 5th year anniversary - ten years: 160 hours
1/1 following 10th year anniversary and beyond: 200 hours

Hired before 1/1/93
Employees who have completed 20 years of service as of 12/31/12: 240 hours

Another way of stating the above:
If hired between January 1st and June 30 they get 40 hours, if hired between July 1st
and December 31st, they get nothing until January 1st of following year; then on
January 1st after hired in previous year they would get 104 hours.

If their 5th year anniversary falls anytime within a year, then the following year
on January 1st they will get 144 hours.

If their 10th year anniversary falls anytime within a year, then the following year on January 1st they will
get 184 hours.

And if they are not grandfathered in they would go by the above. 5 years 5 months, they would receive 144 hours on the following January
1st.

I also have a FLOATINGHILIDAY table that has years and a check box if there is a FLOATINGHOLIDAY for that year. I need that to automatically update each year so it will show on the report. They don’t receive a Floating Holiday every year.

And how do I get carryover to update at the beginning of the year. Meaning whatever paid time off is left over gets carried over to the next year. I manually entered the carryover for from last year, so I don’t want to lose that information for this year, but don’t want it to be a factor for next year if it was used.

I have attached my database as a zip file.

[JamesDeckert]

You're trying to do something like have an age field for a person. It's a calculation which will change over time. So the longer someone is employed the PTOCEILING field will change. Thus autofilling the field won't be a one time process. A better solution would be for a date field in tblPTODESC. If the employee is hired prior to the date in tblPTODESC, then they belong to one of three specific plans, and the years of service is calculated from the hire date. Then the appropriate plan can be displayed on your form or report, but there's no need to change it as it's calculated from the hire date.

Floating Holiday - I'm not clear enough on how you want this to work to throw out ideas.

Carryover - Can you carryover hours forever? If so, why carry over hours and not just sum how many have been used and subtract it from how many the employee has earned? Otherwise you could add a button which goes through all employees records and does the calculation and puts the total in the carryover field. I can't see this being an automatic process, seems like someone should push the button at the end of the year.

[ladyrider696]

I have just set up queries that show the accrued start date as 1/1/**, such as 7/31/2015, then 1 year would be 1/1/2016, 5 years would be 1/1/2021, 10 years 1/1/2026 for their accrued paid time off. I just don't know the best way to use that to automatically update the PTO ACCRUED FIELD in my Employees form. I can reload the database if that would help you see what I did.

[JamesDeckert]

lady
I put a label field to the right of your employee form to show you what I was talking about. It calculates the value to be put in the label automatically instead of putting it in manually. There is VBA code in the Current event for the form which does the calculation. Maybe this will help.
James
ATTENDANCEdataTEMPLATE.zip