Solved

Vacation Accrual Calculation using MS-Access

Posted on 2016-09-02
8
61 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 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points (awarded by participants)
Comment Utility
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 18

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points (awarded by participants)
Comment Utility
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)
Comment Utility
generous vacation plan, can I come work for you?
0
 

Author Comment

by:Gerard James
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 18

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points (awarded by participants)
Comment Utility
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
Comment Utility
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 18
Comment Utility
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 18
Comment Utility
poster was lost implementing VBA
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now