Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger alexpereira's Avatar
    Join Date
    Aug 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    vacation leave accrual DB (Access2003/sp2)

    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.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vacation leave accrual DB (Access2003/sp2)

    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
    Jerry

  3. #3
    New Lounger alexpereira's Avatar
    Join Date
    Aug 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: vacation leave accrual DB (Access2003/sp2)

    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?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vacation leave accrual DB (Access2003/sp2)

    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
    Jerry

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vacation leave accrual DB (Access2003/sp2)

    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
    Jerry

  6. #6
    New Lounger alexpereira's Avatar
    Join Date
    Aug 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: vacation leave accrual DB (Access2003/sp2)

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

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vacation leave accrual DB (Access2003/sp2)

    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:>
    Jerry

  8. #8
    New Lounger alexpereira's Avatar
    Join Date
    Aug 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: vacation leave accrual DB (Access2003/sp2)

    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.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vacation leave accrual DB (Access2003/sp2)

    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>
    Jerry

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: vacation leave accrual DB (Access2003/sp2)

    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?
    Regards
    John



  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: vacation leave accrual DB (Access2003/sp2)

    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.

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: vacation leave accrual DB (Access2003/sp2)

    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.
    Regards
    John



  13. #13
    New Lounger alexpereira's Avatar
    Join Date
    Aug 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: vacation leave accrual DB (Access2003/sp2)

    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.

  14. #14
    New Lounger alexpereira's Avatar
    Join Date
    Aug 2003
    Location
    Washington, District Of Columbia, USA
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: vacation leave accrual DB (Access2003/sp2)

    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?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: vacation leave accrual DB (Access2003/sp2)

    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •