The dreaded "Vacation Accrual" queston. (1 Viewer)

[The_Vincester]

Please bear with me.

I'm fairly new to access, and have learned much in the past three weeks I've been "learning" how to use it.

I was simply sick of the process that was going on at my place of business where I'm a Manager (I've been there for about 3 months). We keep about 10 Excel forms for the different pieces of information (production numbers and such) we keep for each individual in the department. There are 115 people, 3 shifts working 24 hours a day. Each morning I would go in and open each excel file to analyze what happened the day before.

We're also tracking an individual's days off, tardiness, quality... you name it.

I finally decided it was time for a database... I had worked with them in the past, but I had never actually "built" one. I've actually knocked out a significant portion of what needs to be done, and I'm proud of my product thus far... except for one thing:

Vacation accrual is "kicking my posterior"!

I've searched these forums (finding some answers, most of which I don't understand), but I almost need to be handed the answers. My other struggles I've overcome with the help of these very forums and Google.

I'll lay out our policy and what I need and hopefully someone can help me. As I learn access (and I will!), maybe one day I could come back here and help someone else that may be in need.

Our policy is as follows:

90 Days to be eligible for accrual.

1-3 years of service - 6.67 Hours a Month (10 Day a year or 80 Work Hours... all the same, and whichever makes things easier)
4-7 years of service 10 hours a month (same deal as above as 15 days or 120 work hours)
+8 years -13.33 hours a month (blah blah)

The hours do NOT carry over, they are all use it or lose it. I can make manual entries when necessary, so it doesn't have to be all "automatic".

Like I said, the searching I've done has come up with some things, but not in the language I yet understand. I do know "modules" and some expressions, but the more that could be "spelled out" the better.

I've got your (what I consider) normal Employee fields, and would like to use something like "DateStarted" as a beginning point, but I'm open to anything.

Thank you VERY much in advance for any help that can be given. This has stalled my project almost a week trying to figure out a solution!

 

[raskew]

Hi -

A little clarification of your procedures, please:

1) Do eligibility periods run from hiring anniversary to hiring anniversary? I would sure hope so because, if it's based on an arbitrary start-month, end-month you'll find yourself having to split the allocation when part of the period is in the 1-3 year category and the balance is in the 4-7 year category.

2) If so, and new hires must wait three months before accrual begins, does that mean that in the first year of employment the employee only accrues 9 * 6.67 hours, or is there a mechanism that retroactively picks up the first 3 months of employment?

3) Can employees borrow on future accruals? If not, then it would seem that an employee must take at least 6.67 hours of vacation in their 12th month of employment, or lose it.

Believe this is a doable, but the business rules must be very clear.

Please post back.

Bob

 

[The_Vincester]

Thanks for your reply Bob!

I'll try to explain as clearly as I can.

1) Employees are hired on as "Temps," needing to complete 90 days of service before being hired on as permanent. Only then can their vacation accrual begin. It is strictly Jan-Dec without rollover. At Jan, the counter is set to 0. People ARE PAID for unused vacation at the end of the year. Also, their 4th year anniversary is when the next "tier" of accrual will start.

2) That's correct, the first year, the very MOST they would be eligible for is 9 days (if hired in January).

3) They CAN take vacation in advance, with all the "pay back if" agreements signed.

I certainly don't "like" the policy, though it was created way over my head and I can't change it. It means we have a lot of people rushing to take their days at the end of the year (oh, they only get HALF PAY for unused vacation). That said, two weeks (the 10 days are almost always taken with weekends) vacation is not bad for being with the company one year. I've seen worse. This is a whole other story though, and we're talking about Access, not work policy .

edit: Oh, and thanks again!

 

[raskew]

My condolences! Doesn't seem like a real fun place to work. Let me give this some thought and I'll get back to you. I've got a working solution, just need to sift through and adapt it to your situation. On second thought, illegal immigrants seem like a real tangible possibility.

Best wishes, Bob

 

[The_Vincester]

My condolences! Doesn't seem like a real fun place to work. Let me give this some thought and I'll get back to you. I've got a working solution, just need to sift through and adapt it to your situation. On second thought, illegal immigrants seems like a real tangible possibility.

Best wishes, Bob

Bob, it's funny you say that because I've jokingly made that suggestion at work! I can't say it is too fun for the majority of the workers there... it's a production environment with very little social interaction, but I'm trying to change things (I really have not been there long). The supervisors spend a LOT of time maintaining all the excel files, and I honestly think that when I'm done I'll save 2 to 3 hours a DAY for them. They can then be "real" supervisors and interact with their people. They didn't even have MEETINGS until I got there! If you can really help me get this part done (I'm 80% complete on the other parts), you could have 115 other people thanking you as well.

 

[webmeister]

Isn't it interesting how "archaic" some companies can be? Where I work, you don't start accruing PTO until after your first six months! On top of that, they have someone sitting at the main door who actualy tracks the exact minute you arrive at work (not 7:00 or 7:30, but 07:04, 07:29, etc). It makes me feel like I'm in elementary school, with hallway monitors. Plus, when we have a lunch for company reasons, the division manager yells for everyone to go wash their hands before eating, as if we are children!

Amazing how some companies tend to not focus on what they should, and how some companies haven't achieved a 21st century vision.

I wish you luck with your endeavors! Let me know how it goes.

 

[The_Doc_Man]

Since you cannot accrue hours past 1 year, decide whether you have to ALSO balance overdraw hours at 1 year. If the answer is NO, you have a nightmare on your hands. I.e. if you cannot carry leave beyond a year but can carry leave overdraws past a year, you have two different sets of rules for the same situation based positive/negative values.

This is almost like an inventory system in which a person's pay entry (person X has worked at least the number of hours in the pay period) must be added on a regular basis and removed on a regular basis. The inventory in this case is "leave" hours. Most people take a running-total query for inventory-like systems. But they don't apply a use-it-or-lose-it approach. (In Lousiana this cannot happen because of a state law. Leave doesn't belong to the company - it belongs to the employee. You can't take it off the books unless you pay it in full, and if the employee objects, you have to have carefully written employment rules. Otherwise you have to carry the hours darned near forever.)

I can see several situations for which you must somehow define very precise rules because each will be a new wrinkle.

1. Person never takes vacation, at end of year gets paid. For this person, you have to remove paid vacation from the books but balance it with an extra pay item.

2. Person ALWAYS takes all vacation before year end. For this person, you have to remove vacation from the books by appropriate transactions that will NOT involve a pay item.

3. Person takes some vacation each year, has some left over to be paid. For this person, you have both a pay and a non-pay removal transaction.

4. Person OVERDRAWS vacation in early part of year, but books balance or the person has excess vacation by the end of the year. Like case 2 or 3 but the dates of the leave usage predate the accrual.

5. Person OVERDRAWS vacation in a year, owes you something at the end of the year. This is the tricky one. Here, your person has non-pay removals that have to be balanced AND either the person owes you money out of the last paycheck for the year OR you have to carry forward leave until it balances again.

As to how you do this, you might want to treat leave as an inventory item, perhaps, though the pay-out cases become problematic. The pay-in case (#5) is even worse.

I don't know how you will do this. My best advice is to first write down ALL THE LEAVE RULES on paper. Then, if it ain't written down, it ain't a rule. Get your HR folks involved. (With that many employees, you have at least one or two HR folks.) Once everyone agrees on the rules, analyze this set of rules as a data-flow. Identify entities and actions. For instance, employees are entities. Accruing leave and using leave are actions. Leave (in the abstract) is an entity, probably a child of the employee table.

Get a dry-erase board, some markers, and some sticky notepads. Draw out each table. Now "populate" the tables with employees where the table is drawn on the board and the sticky notes are records in the table. For each action you want to perform, track the data flow to reach the desired result. Take good notes. Eventually you will have an idea of what has to happen to each record to achieve the required results. Once you have it scoped out, you have half a chance of actually implementing it. Until then, no chance.

 

[The_Vincester]

Thanks Doc, and I understand what you saying, but I'm not sure if it does need to be too complicated as far as "rules." Though 115 is a significant amount of people, I'm certainly willing to do some things manually, which may ease some burden on figuring the solution.

We have one HR person, and while she's great, there is not much contact between her and personnel (she stays busy). Maybe I should be a little more clear on my intentions. I just want to be able to track how much vacation a person has used and be able to give them a balance at any time. I will not be handling their pay, or what they will be paid. Though the numbers should "jive" our HR department and payroll will take care of most of the financial dealings.

Our HR doesn't "physically" track things however, it's all based on a web-based clock in/out system and in another state is where the payroll is figured. It's a real mess and if they would only give me access to the admin portion of this web database, I wouldn't be in the situation right now.

My part is that I have to "grant leave" (I'm formally military, so I like using the word "leave" too) while flying somewhat blind. My supervisors have 3 different excel forms JUST for tracking this (three shift, three mindsets... but I'm working on that).

This is actually a multi-million dollar company that is run like a "mom and pop" in some aspects. I'm trying to fix this somewhat. The inefficiency is amazing and it's driving me crazy. We have one excel file that has 97 pages! They have to make a new one each month (well, erase the data and resave).

Being able to track some things like this "easily" independent of HR and their web-based system would certainly help things believe or not.

 

[raskew]

Think I want to retract the previous statement (post #4) about having a working solution. That solution is based on an individual's hire date and their years of service is based on that date, not the arbitrary 1 Jan - 31 Dec.

Out of curiousity, say an individual is hired as a temp on 6/6/06. His 90th day would be 9/3/06. On 9/4/06 he would start his first year of employment. Under your organization's scheme, when would that year end, so he could then begin his second year of employment?

Bob

 

[The_Vincester]

Think I want to retract the previous statement (post #4) about having a working solution. That solution is based on an individual's hire date and their years of service is based on that date, not the arbitrary 1 Jan - 31 Dec.

Out of curiousity, say an individual is hired as a temp on 6/6/06. His 90th day would be 9/3/06. On 9/4/06 he would start his first year of employment. Under your organization's scheme, when would that year end, so he could then begin his second year of employment?

Bob

On 9/4/06 he would begin accruing days at the rate of 6.67 hours a month (that month included) though Dec. He would continue to accrue at that rate all through '07, giving him 10 days. On 09/04/10 (his 4 year mark), he would begin to accrue at the next tier rate. So he would have the first 8 months at 6.67 and the last four at 10 hrs per.

Would it be prudent to have columns for anniversary dates that would "trigger" a new rate? I honestly have no idea, as I honestly can't wrap my head around this thing.

It "seems" fairly simple "on paper" but as Doc pointed out, there are many "rules" that could slide through and cause bumps.

Bob, I really do appreciate you delving into this affair, as I fear it will one day cause me to lose my mind.

 

[The_Doc_Man]

I think you can do this with a transaction-oriented system, but deciding when and how to do the transactions is the bug-a-boo.

Consider these transaction types:

1. You got it: accrue some number of hours. Amount of hours accrued depends on anniversary date and number of years. (Do a DATEDIFF of anniversary date vs current date in units of years to decide the amount?)

2. You use it: burn some number of hours. Amount of hours not dependent on anything but the amount of time used. I.e. "Boss, I want 8 hours." "OK, you take 8 hours"

3. You lose it: Pay for some number of hours. Date of transaction determined by business rules. Amount of transaction dependent on business rules.

Now consider a UNION query:

SELECT hours FROM leavetrans WHERE transtype = "Get" UNION
SELECT -hours FROM leavetrans WHERE transtype = "Use" UNION
SELECT -hours FROM leavetrans WHERE transtype = "Lose" ;

Then do a SUM query GROUP BY employeeID on the UNION query. The total for each employeeID is the leave they have on the books.

This approach depends very strongly on running the "lose it" transactions at the correct time and for the correct amount. So... how do you determine the correct amount? If I read your rules correctly, you can build a table of PROPOSED "lose it" transactions by doing a MAKETABLE query based on the aforementioned SUM query, filtering out cases where the sum of leave is less than zero. This would build a transaction to pay for each employee's current leave balance unless balance is negative. Just store constants in the fields of the MAKETABLE to make them look JUST LIKE the "Lose it" transactions. Append that table to the leave transaction table. (Bear with me, I left a strange item in here that at first glance shouldn't be... but it should.)

Now, let's close the loop...

Cut your pay items based on the contents of the MAKETABLE query. (You have the balances to be paid, after all). SKIP payments for any entry that was zero because the employee (wisely) used all leave before year end.

Now, one or two more queries. When you have the MAKETABLE query into a table, you now have a table of all people whose balance just got ZEROED. So... archive this by doing a copy of all leave records from the leavetrans table to a history table if they are in the JUST_GOT_ZEROED table. Then delete the records you just copied. This removes all records from the leavetrans table for all persons whose balance is zero, whether because you paid for the leave or they used up their leave. It leaves behind all transactions for folks whose balance was negative.

Make sense?