Vacation/Personal Time Off Accrual in MS Access 2007

Hello, I have a MS Excel Spreadsheet that I am currently using to keep track of my personnel's time off but would like to move it into Access. I have read many threads on this issue but most of what I have read is by days and not hours per week. I am wondering if someone could help me using the criteria below:

ACCRUAL RATE
VAC
1-4 YEARS 1.54 HRS WEEKLY
5-14 YEARS 2.31 HRS WEEKLY
AFTER 14th YEAR 3.08 HRS WEEKLY

PER
1-6 YEARS 1.54 HRS WEEKLY
7-9 YEARS 1.69 HRS WEEKLY
AFTER 10 YEARS 1.85 HRS WEEKLY

FLOATING HOLIDAY
1-19 YEARS 1 FLOATING HOLIDAY
20-24 YEARS 2 FLOATING HOLIDAY'S
AFTER 25 YEARS 3 FLOATING HOLIDAY'S

I have created the tblEmployeeInfo with:
EmpID (autonum, Long); EmpBadgeNum (Number, Long); EmpLastName; EmpFirstName; EmpStartDate (Date/Time, Med); and what will be the FK from the accrual table: AccrualRateID (Number, Long)

In the tblAccrualRate is:
AccrualRateID (Autonum, Long); AccrualRate: (Number, Long?); AccrualRateDescription

Thank you in advance for any and all help with this.

The first thing that strikes me about your scenario is that it seems entirely based off number of years employed, is it not? In other words, if I told you the exact date someone in your company started working, could you calculate with 100% certainty how many total hours of vacation they've accrued? If so, I really question whether number of hours Accrued needs to be stored AT ALL (just calculate it as needed).

In that case, I would think the only thing you really need to know is how many hours they've actually used, which is an entirely different discussion.

0

francoeonaAuthor Commented:

Hey there, AccessGuy! Yes, if I knew when a person is hired I could figure out how much time they would have accrued and then would need to deduct the days they took off. I am attaching a screen shot of the spreadsheet and the way it is set up. I know that it is not going to be set up this way in access but have a hard time with the transposition if you know what I mean.... :) It's hard for me to figure out the set up for access without thinking about how it is set up in excel.....it's something I really need to work on. Thanks so much for responding so fast by the way! VAC-PER-BAL-Project.doc

What does the field "Date" represent in this example?

0

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

But only two of the records have any value for Time Taken? What's the purpose of the other ones?

0

francoeonaAuthor Commented:

That is only part of the spreadsheet to show how it is set up. You can see why I want to convert to access......the spreadsheet has to be constintly modified - adding new dates in as we go. This is how the spreadsheet was given to me. It has all dates so you can just put how much time was used on what date and then the calculation in the accumalitive column calculates what is left as well as when it is accrued....make sense? See, an employee can leave 1/2 day or if they are late they must use PER (personal time) to cover. So with the spreadsheet set up the way it is you just enter the amount of hours taken on the day it was taken.

0

francoeonaAuthor Commented:

So I know I need to figure out how to calculate what is accumalated and then just have a field show what the balance is when time is taken. I just don't know how to calculate the time per week....

Well, I don't think you're going to want to have a record for every single day a person accrues time off. If you can calculate from their date of start, then you should be fine as far as that is concerned. That means you would not need the FK AccrualID in your main Employees table and you don't need tblAccrualRate at all. I would add a table called tblTimeOff with the following fields:

You can make a form to make it easier to enter them, but you'll probably need to manually transfer the time off from the spreadsheet.

When I had all the time off records entered, I would create a form and/or report to let me view the time off requests along with their calculated Accrual. I would accomplish the Accrual calculation with a public VBA function that emulated the logic described by your first post and calculated accrual based on start date. I would then add this function to a query which would be the basis of my form or report.

tblFactor:
FactorID (Autonumber, PK)
Factor (Number, Long???) - This will be the factoring criteria for the calculations?
Description (Txt)

I'm thinking that I will need the factor table for figuring the criteria for the calculations right? I'm not sure.....I'm sorry if I am not following what you are saying.....also, I will need an Iff statement for the 120 days and 1 year criteria right? Where would I put the Iff statement and VBA code for the calculations?

Edit: Oh right, I left out the fact that employees start to accrue immediately but cannot take PER until after 120 days and VAC after 1 year....so how would I set the factor so that the calculation will factor for example VAC: if start date = 1-4 years then accrue 1.54 hours a week or .22 hours a day or even 6.16 hours a month? I think I may be overthinking this?

First of all, I would make EmpBadgeNum a standard text field. One good data type rule to follow is, when you're assigning something a numeric data type, ask yourself whether you would ever multiply, divide, add, or subtract the number. Numbers like "Phone Number" and "Zip Code" are better suited to text fields because although typically they're numeric, they could just as easily been made with letters with virtually zero impact on the course of history.

I have attached a basic starting point for you to look at. The key component is the function "fcnCalculateAccruedTimeOff" found in modGlobal. This is not complete, but you can open qryEmployeeAccruals to see it in action:

Public Function fcnCalculateAccruedTimeOff(inStartDate As Date, inAccrualType As String) As Double 'This function will use the passed in Employee Start Date to calculate 'the total time they have accrued off. This function will return the 'number of hours accrued or -1 if an error occurs. 'Turn on error handling On Error GoTo Error_Handler 'Declare variables Dim lngYearsWorked As Long 'Get Years worked lngYearsWorked = DateDiff("yyyy", inStartDate, Date) 'Check the Accrual Type If inAccrualType = "PER" Then 'Check number of years worked If lngYearsWorked <= 6 Then 'Return 80.08 per year fcnCalculateAccruedTimeOff = lngYearsWorked * 80.08 ElseIf lngYearsWorked >= 7 And lngYearsWorked <= 9 Then 'Return 80.08 per year for the first 6 (480.48), then 87.88 for 'each year from 7 - 9 fcnCalculateAccruedTimeOff = 480.48 + ((lngYearsWorked - 6) * 87.88) ElseIf lngYearsWorked >= 10 Then 'Return 80.08 per year for the first 6 (480.48), 87.88 for years '7 - 9 (263.64), and 96.2 per year for each year 10 and after. fcnCalculateAccruedTimeOff = 480.48 + 263.64 + ((lngYearsWorked - 9) * 96.2) End If ElseIf inAccrualType = "VAC" Then 'Check number of years worked If lngYearsWorked <= 4 Then 'Return 80.08 per year fcnCalculateAccruedTimeOff = lngYearsWorked * 80.08 ElseIf lngYearsWorked >= 5 And lngYearsWorked <= 14 Then 'Return 80.08 per year for the first 4 (320.32), then 120.12 for 'each year from 5 - 14. fcnCalculateAccruedTimeOff = 320.32 + ((lngYearsWorked - 4) * 120.12) ElseIf lngYearsWorked >= 15 Then 'Return 80.08 per year for the first 4 (320.32, 120.12 for years '5 - 14 (1201.2), and 160.12 per year for each year 14 and after. fcnCalculateAccruedTimeOff = 480.48 + 263.64 + ((lngYearsWorked - 14) * 160.16) End If End If 'Exit before error handling Exit FunctionError_Handler: 'Return -1 fcnCalculateAccruedTimeOff = -1End Function

The reason the above is incomplete is because right now it's just basing the returned values on whole years. This is primarily because I'm not 100% which logic I should be using. I could base the entire thing off the number of weeks, but the sticking point with the logic is what happens when a week falls half in the 3rd year and half in the 4th year? Or can I just assume that, for the purposes of the calculation, they get to the next year after 52 weeks (which means they would get to year four accrual rates for VAC after 156 weeks)?

In regards to the restriction on taking VAC or PER, that seems like something you would just communicate with your staff. If you were having them submit requests for time off through the system, the system could figure it out and restrict them, but I don't think you're doing that right now. TimeOffSample.accdb

0

francoeonaAuthor Commented:

This looks great! You are so the BEST! I think going with basing the returned values on whole years should work fine.....

One question though; How would I calculate hours into it? Say like if a person goes home early or only wants to take 4 hours VAC?

The query I provided only shows what has been accrued. Also, I know you think it LOOKS awesome but your users might complain if they are at 3 years and 51 weeks and your system is only showing 3 years worth of accrued time. You should really consider getting it a little more "fine tuned" and accurate than what I have provided so far.

"How would I calculate hours into it?"

I'm going to assume you're asking how to subtract what time has been taken off here. There's a bunch of ways you can do it. I've reattached my sample with four new queries:

One approach would be to add two subforms, one for PER and one for VAC, which are based on qryPERHoursRemaining and qryVACHoursRemaining. TimeOffSample.accdb

0

francoeonaAuthor Commented:

Thank you so much for all your help! I will do what you have advised and if I need aditional help I will be back.... :)

Edit: Before I let you go, I have to let you know that I didn't change a number which I intended to in one of your calculations:

ElseIf lngYearsWorked >= 15 Then 'Return 80.08 per year for the first 4 (320.32, 120.12 for years '5 - 14 (1201.2), and 160.12 per year for each year 14 and after. fcnCalculateAccruedTimeOff = 480.48 + 263.64 + ((lngYearsWorked - 14) * 160.16)

ElseIf lngYearsWorked >= 15 Then 'Return 80.08 per year for the first 4 (320.32, 120.12 for years '5 - 14 (1201.2), and 160.12 per year for each year 14 and after. fcnCalculateAccruedTimeOff = 320.32 + 1201.2 + ((lngYearsWorked - 14) * 160.16)

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

In that case, I would think the only thing you really need to know is how many hours they've actually used, which is an entirely different discussion.