Solved

Vacation Accrual Calculation using MS-Access

Posted on 2016-09-02
8
77 Views
Last Modified: 2016-10-07
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
Comment
Question by:Gerard James
8 Comments
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 250 total points (awarded by participants)
ID: 41781981
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
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points (awarded by participants)
ID: 41782004
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41782332
generous vacation plan, can I come work for you?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:Gerard James
ID: 41782919
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
 
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points (awarded by participants)
ID: 41783006
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
 

Author Comment

by:Gerard James
ID: 41785434
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
 
LVL 19
ID: 41785536
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
 
LVL 19
ID: 41833316
poster was lost implementing VBA
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question