[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

Vacation Accrual Calculation using MS-Access

I am trying to construct an Access database that will allow for the calculation of vacation days accrued for employees using the criterion below

Status = Established
under $25,692 per year = 21 days (1.75 per month)
$25,692 and under $35,544 per year = 28 days (2.3 per month)
$35,544 per year and over = 35 days (2.9 per month)

Status = Un-established
1 - 2 years service = 14 working days
3 - 5 years service = 21 working days
6 - 10 years service = 28 working days
over 10 years service= 35 working days

Status = Contracted
under $24,000 per year = 15 working days
$24,000 to $41,988 per year = 20 working days
over $41,988 per year = 25 working days

It is hoped that the employee status and salary would be somehow used to determine the vacation days they are entitled to. The accrued days will also be deducted from holidays granted to employees and recorded in a calculated filed to ensure an accurate balance is kept. Can anyone help?

The basic staff table has the following fields:
Staff_ID
Name
Division
Salary
Status
Start Date
0
Gerard James
Asked:
Gerard James
3 Solutions
 
PatHartmanCommented:
Are you planning to run the accrual at the end of each month?  What salary and status would you use?  The ones effective on the first day of the month or the ones effective on the last day of the month?  Why use both status and salary?  What if they conflict?

You need a table to store vacation accrued and vacation used.  So each month a new accrual record would be added and each time the employee uses vacation time a new record would be added using negative amounts so the table can be summed to get the current balance for an employee.

What have you got so far?
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
here is a general function you can call to calculate vacation days. If your logic to calculate years of service is different, you can modify the code.
Function GetVacationDays( _
              psStatus As String _
             , pcSalary As Currency _
             , pDtStart As Date _
             , Optional pdblDaysPerMonth As Double _
                ) As Integer

'160902 strive4peace
   'PARAMETERS
   '  psStatus is the status
   '  pcSalary is the salary
   '  pDtStart is the start date
   '  pdblDaysPerMonth is returned -- send if you want it back
   'RETURNS
   '  working days of vacation
   
   'set up error handler
   On Error GoTo Proc_Err
   
   'initialize return value
   GetVacationDays = 0
   pdblDaysPerMonth = 0
   
   'dimension variables
   Dim iYearsService As Integer
   
   'calculate years of service
   iYearsService = DateDiff("yyyy", pDtStart, Date)
   'subtract 1 from years of service if start date is later in the year than today
   If DateSerial(Year(Date), Month(pDtStart), Day(pDtStart)) > Date Then
      iYearsService = iYearsService - 1
   End If
   
   Select Case psStatus
   Case "Established"
      If pcSalary >= 35544 Then
         pdblDaysPerMonth = 2.9
         GetVacationDays = 35
      ElseIf pcSalary >= 25692 Then
         pdblDaysPerMonth = 2.3
         GetVacationDays = 28
      Else
         pdblDaysPerMonth = 1.75
         GetVacationDays = 21
      End If
   Case "Un-established"
      If iYearsService >= 10 Then
         GetVacationDays = 35
      ElseIf iYearsService >= 6 Then
         GetVacationDays = 28
      ElseIf iYearsService >= 3 Then
         GetVacationDays = 21
      Else
         GetVacationDays = 14
      End If
         
   Case "Contracted"
      If pcSalary >= 41988 Then
         GetVacationDays = 25
      ElseIf pcSalary >= 24000 Then
         GetVacationDays = 20
      Else
         GetVacationDays = 15
      End If
   End Select 'status
   
Proc_Exit:
   On Error Resume Next
   Exit Function
  
Proc_Err:
  'comment next line with MsgBox after this works ok so it just exits if there is an error
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetVacationDays"

   Resume Proc_Exit
   Resume
End Function

Open in new window

1
 
Dale FyeCommented:
generous vacation plan, can I come work for you?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Gerard JamesAuthor Commented:
Thank you so much for your speedy response but I am afraid that I am still a bit lost. I have been trying to run the function in a query but not sure of the syntax and getting no results. I am sure that the error is on my part - can anyone assist.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
in a query, try this:
MyColumnName: GetVacationDays( [Status], [Salary], [Start Date],0)

Open in new window

WHERE
MyColumnName is whatever you want to call the column (perhaps VacationDays)

As written, you will not be able to get the DaysPerMonth returned since it will require launching from code to grab a second value.

to launch from a query,
Optional pdblDaysPerMonth
may need to change to
Optional ByVal pdblDaysPerMonth
... if it can even be included

be sure to Debug,Compile, and Save the module before you use the function
0
 
Gerard JamesAuthor Commented:
So far I have nothing. I was hoping to get a semi-complete solution that could be modified to fit my needs.

The accrual should run at the end of each month and would be based on both the employee status and the salary earned. A separate table is required to document the running balance and all deductions made over time (something like a sub-form I guess).

Any help that can be provide inclusive of the additional table structures will be greatly appreciated. I am an Access rookie and this solution is gravely needed. Thanks again for your help.

Thanks Crystal.. I really do appreciate the solution provided.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Gerard,

> "Access rookie"

I guess the VBA code is kind of like being in the ocean when you don't know how to swim ...

instead of using a query to start ... maybe a form? Create a command button on the form and set these properties:

Name = cmdTest
Caption = Test

when you are in the Design View of a form, whatever you click on will show values in the Property sheet. Explore what you can set.

If the Property sheet is not displayed, press Alt-Enter to turn it on

Once you have created the command button ... that actually doesn't do anything yet, post back for the next step
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
poster was lost implementing VBA
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now