Solved

# Vacation Accrual Calculation using MS-Access

Posted on 2016-09-02
95 Views
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
Question by:Gerard James
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 37

Assisted Solution

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 20

Accepted Solution

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

'calculate years of service
'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
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"
GetVacationDays = 35
GetVacationDays = 28
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
``````
1

LVL 47

Expert Comment

ID: 41782332
generous vacation plan, can I come work for you?
0

Author Comment

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 20

Assisted Solution

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)
``````
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

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 20

Expert Comment

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 20

Expert Comment

ID: 41833316
poster was lost implementing VBA
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
###### Suggested Courses
Course of the Month8 days, 13 hours left to enroll