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.

Fran
francoeonaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AccessGuy1763Commented:
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
0
AccessGuy1763Commented:
What does the field "Date" represent in this example?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

francoeonaAuthor Commented:
That would be DateTimeTaken
0
AccessGuy1763Commented:
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....
0
AccessGuy1763Commented:
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:

EmpID (long) PK (composite)
TimeOffDate (date/time) PK (composite)
HoursTaken (int)
TimeOffType (Text)

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.
0
francoeonaAuthor Commented:
Good Morning! OK, so now I have:

tblEmpInfo:
EmpID (Autonumber, PK)
EmpBadgeNum (Number, Long)
EmpFirstName (Txt)
EmpLastName (Txt)
EmpStartDate (Date/Time, Med)

tblTimeOff:
TimeOffID (Autonumber, PK)
EmpID (Number, Long, FK)
TimeOffDate (Date/Time, Med)
HoursTaken (Number, Long)
TimeOffType (Txt)

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?
0
AccessGuy1763Commented:
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 Function
    
Error_Handler:

    'Return -1
    fcnCalculateAccruedTimeOff = -1
    
End Function

Open in new window


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?
0
AccessGuy1763Commented:
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:

qryPERHoursTakenAndAccrual
qryVACHoursTakenAndAccrual
qryPERHoursRemaining
qryVACHoursRemaining

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.... :)
0
AccessGuy1763Commented:
Good luck to you and you're welcome! :)

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)

Open in new window


Should be:

        
   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)

Open in new window


Again, I would spend some time fine tuning and testing this function... it's pretty critical to this whole thing working properly!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.