Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Hello, I have a MS Excel Spreadsheet that I am currently using to keep track of my personnel's time off but would like to move it into Access. I have read many threads on this issue but most of what I have read is by days and not hours per week. I am wondering if someone could help me using the criteria below:

ACCRUAL RATE

VAC

1-4 YEARS 1.54 HRS WEEKLY

5-14 YEARS 2.31 HRS WEEKLY

AFTER 14th YEAR 3.08 HRS WEEKLY

PER

1-6 YEARS 1.54 HRS WEEKLY

7-9 YEARS 1.69 HRS WEEKLY

AFTER 10 YEARS 1.85 HRS WEEKLY

FLOATING HOLIDAY

1-19 YEARS 1 FLOATING HOLIDAY

20-24 YEARS 2 FLOATING HOLIDAY'S

AFTER 25 YEARS 3 FLOATING HOLIDAY'S

I have created the tblEmployeeInfo with:

EmpID (autonum, Long); EmpBadgeNum (Number, Long); EmpLastName; EmpFirstName; EmpStartDate (Date/Time, Med); and what will be the FK from the accrual table: AccrualRateID (Number, Long)

In the tblAccrualRate is:

AccrualRateID (Autonum, Long); AccrualRate: (Number, Long?); AccrualRateDescription

Thank you in advance for any and all help with this.

Fran

ACCRUAL RATE

VAC

1-4 YEARS 1.54 HRS WEEKLY

5-14 YEARS 2.31 HRS WEEKLY

AFTER 14th YEAR 3.08 HRS WEEKLY

PER

1-6 YEARS 1.54 HRS WEEKLY

7-9 YEARS 1.69 HRS WEEKLY

AFTER 10 YEARS 1.85 HRS WEEKLY

FLOATING HOLIDAY

1-19 YEARS 1 FLOATING HOLIDAY

20-24 YEARS 2 FLOATING HOLIDAY'S

AFTER 25 YEARS 3 FLOATING HOLIDAY'S

I have created the tblEmployeeInfo with:

EmpID (autonum, Long); EmpBadgeNum (Number, Long); EmpLastName; EmpFirstName; EmpStartDate (Date/Time, Med); and what will be the FK from the accrual table: AccrualRateID (Number, Long)

In the tblAccrualRate is:

AccrualRateID (Autonum, Long); AccrualRate: (Number, Long?); AccrualRateDescription

Thank you in advance for any and all help with this.

Fran

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

VAC-PER-BAL-Project.doc

EmpID (long) PK (composite)

TimeOffDate (date/time) PK (composite)

HoursTaken (int)

TimeOffType (Text)

You can make a form to make it easier to enter them, but you'll probably need to manually transfer the time off from the spreadsheet.

When I had all the time off records entered, I would create a form and/or report to let me view the time off requests along with their calculated Accrual. I would accomplish the Accrual calculation with a public VBA function that emulated the logic described by your first post and calculated accrual based on start date. I would then add this function to a query which would be the basis of my form or report.

tblEmpInfo:

EmpID (Autonumber, PK)

EmpBadgeNum (Number, Long)

EmpFirstName (Txt)

EmpLastName (Txt)

EmpStartDate (Date/Time, Med)

tblTimeOff:

TimeOffID (Autonumber, PK)

EmpID (Number, Long, FK)

TimeOffDate (Date/Time, Med)

HoursTaken (Number, Long)

TimeOffType (Txt)

tblFactor:

FactorID (Autonumber, PK)

Factor (Number, Long???) - This will be the factoring criteria for the calculations?

Description (Txt)

I'm thinking that I will need the factor table for figuring the criteria for the calculations right? I'm not sure.....I'm sorry if I am not following what you are saying.....also, I will need an Iff statement for the 120 days and 1 year criteria right? Where would I put the Iff statement and VBA code for the calculations?

Edit: Oh right, I left out the fact that employees start to accrue immediately but cannot take PER until after 120 days and VAC after 1 year....so how would I set the factor so that the calculation will factor for example VAC: if start date = 1-4 years then accrue 1.54 hours a week or .22 hours a day or even 6.16 hours a month? I think I may be overthinking this?

I have attached a basic starting point for you to look at. The key component is the function "fcnCalculateAccruedTimeOf

```
Public Function fcnCalculateAccruedTimeOff(inStartDate As Date, inAccrualType As String) As Double
'This function will use the passed in Employee Start Date to calculate
'the total time they have accrued off. This function will return the
'number of hours accrued or -1 if an error occurs.
'Turn on error handling
On Error GoTo Error_Handler
'Declare variables
Dim lngYearsWorked As Long
'Get Years worked
lngYearsWorked = DateDiff("yyyy", inStartDate, Date)
'Check the Accrual Type
If inAccrualType = "PER" Then
'Check number of years worked
If lngYearsWorked <= 6 Then
'Return 80.08 per year
fcnCalculateAccruedTimeOff = lngYearsWorked * 80.08
ElseIf lngYearsWorked >= 7 And lngYearsWorked <= 9 Then
'Return 80.08 per year for the first 6 (480.48), then 87.88 for
'each year from 7 - 9
fcnCalculateAccruedTimeOff = 480.48 + ((lngYearsWorked - 6) * 87.88)
ElseIf lngYearsWorked >= 10 Then
'Return 80.08 per year for the first 6 (480.48), 87.88 for years
'7 - 9 (263.64), and 96.2 per year for each year 10 and after.
fcnCalculateAccruedTimeOff = 480.48 + 263.64 + ((lngYearsWorked - 9) * 96.2)
End If
ElseIf inAccrualType = "VAC" Then
'Check number of years worked
If lngYearsWorked <= 4 Then
'Return 80.08 per year
fcnCalculateAccruedTimeOff = lngYearsWorked * 80.08
ElseIf lngYearsWorked >= 5 And lngYearsWorked <= 14 Then
'Return 80.08 per year for the first 4 (320.32), then 120.12 for
'each year from 5 - 14.
fcnCalculateAccruedTimeOff = 320.32 + ((lngYearsWorked - 4) * 120.12)
ElseIf lngYearsWorked >= 15 Then
'Return 80.08 per year for the first 4 (320.32, 120.12 for years
'5 - 14 (1201.2), and 160.12 per year for each year 14 and after.
fcnCalculateAccruedTimeOff = 480.48 + 263.64 + ((lngYearsWorked - 14) * 160.16)
End If
End If
'Exit before error handling
Exit Function
Error_Handler:
'Return -1
fcnCalculateAccruedTimeOff = -1
End Function
```

The reason the above is incomplete is because right now it's just basing the returned values on whole years. This is primarily because I'm not 100% which logic I should be using. I could base the entire thing off the number of weeks, but the sticking point with the logic is what happens when a week falls half in the 3rd year and half in the 4th year? Or can I just assume that, for the purposes of the calculation, they get to the next year after 52 weeks (which means they would get to year four accrual rates for VAC after 156 weeks)?

In regards to the restriction on taking VAC or PER, that seems like something you would just communicate with your staff. If you were having them submit requests for time off through the system, the system could figure it out and restrict them, but I don't think you're doing that right now.

TimeOffSample.accdb

One question though; How would I calculate hours into it? Say like if a person goes home early or only wants to take 4 hours VAC?

"How would I calculate hours into it?"

I'm going to assume you're asking how to subtract what time has been taken off here. There's a bunch of ways you can do it. I've reattached my sample with four new queries:

qryPERHoursTakenAndAccrual

qryVACHoursTakenAndAccrual

qryPERHoursRemaining

qryVACHoursRemaining

One approach would be to add two subforms, one for PER and one for VAC, which are based on qryPERHoursRemaining and qryVACHoursRemaining.

TimeOffSample.accdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialEdit: Before I let you go, I have to let you know that I didn't change a number which I intended to in one of your calculations:

```
ElseIf lngYearsWorked >= 15 Then
'Return 80.08 per year for the first 4 (320.32, 120.12 for years
'5 - 14 (1201.2), and 160.12 per year for each year 14 and after.
fcnCalculateAccruedTimeOff = 480.48 + 263.64 + ((lngYearsWorked - 14) * 160.16)
```

Should be:

```
ElseIf lngYearsWorked >= 15 Then
'Return 80.08 per year for the first 4 (320.32, 120.12 for years
'5 - 14 (1201.2), and 160.12 per year for each year 14 and after.
fcnCalculateAccruedTimeOff = 320.32 + 1201.2 + ((lngYearsWorked - 14) * 160.16)
```

Again, I would spend some time fine tuning and testing this function... it's pretty critical to this whole thing working properly!

Microsoft Access

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

In that case, I would think the only thing you really need to know is how many hours they've actually used, which is an entirely different discussion.