Vacation Database Remaining Days Ideas

[rmulder]

I have a vacation database that is coming along well but I am now to the tough part. I need to come up with a way to track the remaining days a employee has.

I am having a tough time grasping the best way to implement a debit system of sorts. I would like it to be as automated as possible but with my knowledge of vba being small, I know it's going to be tough.

Right now I have time off "records" linked to "employees" records. Any general ideas on how to create as simple as possible debit system for say vacation days? I am allowing a "time off" be given a type of vacation, personal, sick, etc....

I know I'm being general, just looking for a jumping point to start off and ideas

 

[gemma-the-husky]

you store the days taken off in a series of "vacation records". You store the total entitlement somewhere - maybe in the employee record, for sake of simplicity.

Then you sum the days used in the appropriate vacation records.

 

[Galaxiom]

The TimeOff table fields:
EmployeeID, DateStart, DateEnd, LeaveType

Use a query to calculate the entitlements of each LeaveType for the employee based on their work history and deduct the sum of the leave taken grouped by the EmployeeID and LeaveType.

What you definitely don't do is maintain a net entitlement value in a table.

 

[Galaxiom]

You store the total entitlement somewhere - maybe in the employee record, for sake of simplicity.

Very bad idea. Denormalization alert. The entitlement can be calculated from their employment history and the leave entitlement formula.

Entering the entitlement manually is fraught with potential error and will always be out of date.

 

[rmulder]

what if we have employees that have been given more vacation time before the normal formula for say average employee would have gotten the bump? it seems really tough. ill keep thinking on it. thanks for the suggestions.

 

[Galaxiom]

Maintain a table of records for extra entitlements granted.
If it is never going to happen again then store an opening balance in their employee record.

 

[rmulder]

Ok, I guess I'll start simple and expand. Galaxiom, how would I go about creating the query that calculates the "entitlements of each LeaveType". Lets say I have Vacation, Sick, and Personal for leave types.

Each employees vacation entitlement depends on the years of service from their start date which is in the Employee record. After 6 months, its 3 days. After 1 year they get 2 more days for a total of 5. After 3 years a employee is up to 10 days. After 7 years, 15 days. 15 years is 20 days.

The sick and personal days are the same for all employees.

 

[rmulder]

My idea to accomplish the "calculated query" would be a two step process.

First write a "public function" of sorts in vba with arguments like for example.
Public Sub Vacation_Allowance (start_date, extra days given) - told you i'm bad at vba....

and through calculation have this return the total days allowed for that employee for their year.

Then in the query builder, have a calculated field like "Vacation Days : Vacation_Allowance([Start_Date], [Extra_Days])

I know my syntax is way off but I think that's how you'd do it. Anyone want to help me with syntax and getting it working?

 

[Galaxiom]

I would put that in a custom function.

Public Function Entitlement(StartDate As Date)
 
Dim MonthsService As Integer
 
   MonthsService = DateDiff("m",[StartDate], Date())
 
   SelectCase MonthsService
       Case Is >= 12 * 15
          Entitlement = 20
          Exit Function
       Case Is >= 12 * 7
          Entitlement =  15
          Exit Function
etc
End Function

If the entitlement is different for different LeaveTypes then add an argument to indicate that and process accordingly.

If the formula changes you will need to modify the function to suit, taking into account the service before and after the change.

Put this function in a Standard Module and call it from the query.

SickLeave: Entitlement([StartDatefield])

 

[rmulder]

Ok I've got a query that calculates the "entitled days" for the employees correctly now. I've also got a query of my "time off" records that calculates the "days to debit" for each record excluding weekends and holidays. I'm getting closer!!!!

Now, where should I head from here? I know I need to sum the "days to debit" of each type and then subtract that from the previously calculated fields and display in a query.

for simplicity, doing the types one at a time I'm sure makes sense.

Please help me with the coding. Thanks so very much!!!!

 

[rmulder]

I mean, I guess I need the "Employees_with_entitlement" query as well as the "Time_off_with_days_debit" query in a separate new query. I then need to somehow go through the "time_off" query and sum the "days_to_debt" for type "vacation" for each employee. That would need to be subtracted from the "Employees_entitlement".

To make it more complicated. We'd only be interested in summing the "time_off" records that fall between the employees current vacation year. Their current vacation year being the previous "hire_date_month_day" to the upcoming "hire_date_month_day" of the current year.

I am quickly getting overwhelmed

 

[gemma-the-husky]

G

I think you are splitting hairs. I would definiltely store the holiday enitilement. How would it necessarily be derived in the system? Maybe it's all evaluated manually.

Even if the system could calculate it, you need probably need a complex set of tables to evaluate it.

What I would do, is store the entitlement as another transaction type in the holiday taken table (as opposed to the "simpler" method of storing it in the employee record). That is what I was referrring to.

 

[Galaxiom]

Ideally you would enter the leave records from the old system so that they are included in the calculations. This is generally fairly easy if they are in a spreadsheet but not really practical if it is a paper based system.

Of course you would have to check that the current calculation matchs the stated leave balances. It would not be unusual for old systems to have errors so you might need to include a "fudge" figure to align them. Telling someone they are entitled to less leave than they thought because of an error in 1992 is not usally good for empolyee relations.

If entering historical data is not practical then store an opening balance of the leave taken before the database records start.

Now for the balance query. Doing them all at once is very little more than doing just one.

Create a Aggregate (AKA Totals) query. Join the TimeOff table and Entitlement query on EmployeeID and LeaveType.

Derive a LeaveBalance field by subtracting the DaysTaken from the EntitlementDays. The Groups will take care of connecting the right leave types and employee.

Untick the Show box on the Entitlement and TimeOff fields just leaving the EmployeeID, LeaveType and LeaveBalance fields. (The LeaveBalance Totals setting is "Expression")

The query will provide a record of the balance of each LeaveType for each EmployeeID.

 

[Galaxiom]

I would definiltely store the holiday enitilement. How would it necessarily be derived in the system? Maybe it's all evaluated manually.

Even if the system could calculate it, you need probably need a complex set of tables to evaluate it.

Not really a matter of "even if". If it can be evaluated manually it can be calculated by the database. The major difference is the database will always get it right provided the algorithm is correctly implimented.

In the unlikely case of it needing a "complex set of tables" it is still better to make the investment to do it. Maintaining manual systems continues to cost the client perpetually not only to enter the data but to audit it as being correct and up to date.

However I do acknowledge the entry of entitlement records is a valid alternative though not always practical. In Australia, leave entitlement accrues continuously rather than annually so it would have to be entered at every pay period.

 

[rmulder]

I will be attempting getting the calculations working today. Question though, how would I best deal with each person having a different vacation year that depends on their start date? My idea to deal with it is somehow calculate the employees current vacation year in the employees query as calculated fields.

For example. Say my start date is 6/2/2001. My "vacation year" is going to be 6/2/11 to 6/2/12. Now I need to work on a formula to calculate this correctly for each employee.

Once I have that, in the aggregate query, I'd need to use those dates as a filter.

Does it seem like I'm on the right track? I am still a access noob so I like to take it one step at a time.

 

[rmulder]

In my Employees query I added two more calculated fields [Vaca_Year_Start] and [Vaca_Year_End] so I'd have a range for each employee that I could play with.

I have never used the "Totals" before. Sooo I'm trying to learn. I got it doing the subtraction correct by using the expression "Vaca_Left: [Yearly_Vacation]-Sum([Vaca_Days])". Everything else I try throws me a stupid error like "you tried to execute a query that does not include the specified expression as part of the aggregate function."

I added "[Date_Start]>=[Vaca_Year_Start] And [Date_End]<=[Vaca_Year_End]" to the criteria field under the expression field above but I get the error.

 

[Galaxiom]

The code I posted earlier should work on the number of months service including the partial month.

The following line is adjusted so complete months are required. The full years of service are taken care of in the case statement.

MonthsService = DateDiff("m",[StartDate], Date()) + (Day(Date()) < Day(StartDate))

This works because Logical True is (-1) and False is (0). Consequently, if the day of the date is less than the day of the StartDate then the count of months will be reduced by one.

It isn't perfect for someone who starts on 31 of the month because it won't roll over on 30 day months. Similarly for February those who start on 30th or 31st of the month.

The function will need to be adjusted to account for this but I haven't thought about the details.

It may or may not be useful but realise that the "zeroth" of a month is the last day of the previous month.

DateSerial(2011, 3, -1) is the 28th of Feb (or the 29th on leap years).