Accrual, take II

[Claire]

I've tried to reply to the following pasted post, but for some reason the
group is spiting me, and it hasn't appeared all day long. So, if something
finally kicks in and all of these appear, I deeply apologize. That said, I'm
stuck, and keep hitting a wall, so would LOVE some more help on this issue.

I'm working on accruing vacation time. There are different levels of
vacation, and an employee can move to the next level after they've worked a
number of hours at their starting level (this number of hours varies by
level). Each level also has a different rate of accrual and a max number of
hours that can be accrued. Employees start at different levels, and there
are only a certain number of levels. And to make it even more exciting,
hourly and salaried workers have different accruals (salaried depending on
how long they've worked) .

Currently I have two tables, one for hourly and one for salaried, which list
the levels, an employee table with their exemption type (hourly or salaried),
hours worked at the current level carried in (from before this database),
vacation carried in (also from before the database), and starting level,
beginning and ending dates of employment, and a table which sums the
applicable hours for accrual and hours taken off, by employee #.

The Vacation level table looks like:
Level--# Hours before next level--Earn Rate----Earn Period---Max accumulated
1----------4,000---------------------1-----------50------------60
2----------6,000---------------------2-----------50------------120
3---------10,000---------------------3-----------50------------180
4-----------n/a-----------------------4-----------50------------240


My query in progress gives me the rate for level 4+ (these levels are
static), and just lists the rest at level 1 at this point, because I can't
figure out how to change rates without insane if statements in the where.
(ETO = earned time off, ie vacation)

SELECT [ETO Applicable Hours].[Employee #], [ETO Applicable Hours].[Employee
Name], Employees.Exemption, Employees.[Starting Rate], Employees.[2008ETO],
Employees.HoursCarriedIn, [ETO Applicable Hours].[Hours Worked], [ETO
Applicable Hours].[ETO Taken], tblHourlyETO_Levels.Hours_Earn_Rate
FROM tblHourlyETO_Levels, Employees INNER JOIN [ETO Applicable Hours] ON
Employees.EmpNum=[ETO Applicable Hours].[Employee #]
WHERE IIf(Employees.[Starting Rate]>3,Employees.[Starting
Rate]=tblHourlyETO_Levels.Level_Number,tblHourlyETO_Levels.Level_Number=1);

The following suggestions from Clifford get me the rate and allows for
changes in rate, but chooses it as if everyone starts at level 1, and the
levels are the total hours worked rather than what you need to work at EACH
level before moving to the next.

So, if you've read this far, thank you, and do you have any suggestions?

Thanks,
Claire

Hi Claire,

Nice question! I think I can help get you pointed in the right
direction, even with some assumptions I made about how the vacation accrual
works.

I created a table named "tblVacation_Levels" with the following fields:

Level_Number
Hours_High
Hours_Earn_Rate
Hours_Earn_Period
Maximum_Hours_Earnable

The table contains essentially the same data as you show, with a few
changes. The Hours_Earn_Period is to hold the 50 in your example. It is
better to put this information into the table than to "hard" code it. That
way you can have differing values for differing levels. And if it ever
changes you only have to change the data, not the query or other code. For
the Maximum_Hours_Earnable of level 4 I entered 2147483647, the maximum value
that can be stored in a long integer column. I also added an additional row,
containing all zeroes. This is needed in order to do a join on the table
with itself between one row and the succeeding row (i.e. level 0 is joined to
level 1). If you want to report people who have not yet worked, use a -1 in
the Hours_High in the level 0 row.

Next a query, named "qryEmployee Total Hours Worked", that summarizes
the total hours worked for each employee. Two columns in mine:

EmployeeID
Total_Hours_Worked

Presumedly you already have something similar.

Finally, the query that reports the vacation hours allowed for each
employee:

SELECT A.EmployeeID, A.Total_Hours_Worked,
.[Maximum_Hours_Earnable]+([Total_Hours_Worked]-.[Hours_High])*[C].[Hours_Earn_Rate]\[C].[Hours_Earn_Period]
AS Vacation_Hours_Temp,
IIf([Vacation_Hours_Temp]>[C].[Maximum_Hours_Earnable],[C].[Maximum_Hours_Earnable],[Vacation_Hours_Temp]) AS Vacation_Hours_Allowed
FROM [qryEmployee Total Hours Worked] AS A, tblVacation_Levels AS B,
tblVacation_Levels AS C
WHERE (((A.Total_Hours_Worked) Between .[Hours_High]+1 And
[C].[Hours_High]) AND ((C.Level_Number)=.[Level_Number]+1))
ORDER BY A.EmployeeID;

You will need to adapt for your table, query and column names. If you
have questions about that post back.

Hope this helps,

Clifford Bass

 

[Clifford Bass]

Hi Claire,

I can take a look, probably late this evening, if someone else does not.

Clifford Bass

 

[Clifford Bass]

Hi Claire,

That the level hours were additive would have been a good thing to
state in the initial post. And that people can start out as something other
than level one. Neither was obvious. Oh well, better late than not. But
that leads to a related question: How is the vacation accumulated when
crossing from one level to another? Say someone who started as level 1 has
5,000 hours total. How would you calculate the vacation? Likewise for
someone with 13,000 hours? How about for someone who started at level 3 and
has 13,000 hours? Any other quirks on how things work that I should know?

Clifford Bass

I've tried to reply to the following pasted post, but for some reason the
group is spiting me, and it hasn't appeared all day long. So, if something
finally kicks in and all of these appear, I deeply apologize. That said, I'm
stuck, and keep hitting a wall, so would LOVE some more help on this issue.

I'm working on accruing vacation time. There are different levels of
vacation, and an employee can move to the next level after they've worked a
number of hours at their starting level (this number of hours varies by
level). Each level also has a different rate of accrual and a max number of
hours that can be accrued. Employees start at different levels, and there
are only a certain number of levels. And to make it even more exciting,
hourly and salaried workers have different accruals (salaried depending on
how long they've worked) .

Currently I have two tables, one for hourly and one for salaried, which list
the levels, an employee table with their exemption type (hourly or salaried),
hours worked at the current level carried in (from before this database),
vacation carried in (also from before the database), and starting level,
beginning and ending dates of employment, and a table which sums the
applicable hours for accrual and hours taken off, by employee #.

The Vacation level table looks like:
Level--# Hours before next level--Earn Rate----Earn Period---Max accumulated
1----------4,000---------------------1-----------50------------60
2----------6,000---------------------2-----------50------------120
3---------10,000---------------------3-----------50------------180
4-----------n/a-----------------------4-----------50------------240


My query in progress gives me the rate for level 4+ (these levels are
static), and just lists the rest at level 1 at this point, because I can't
figure out how to change rates without insane if statements in the where.
(ETO = earned time off, ie vacation)

SELECT [ETO Applicable Hours].[Employee #], [ETO Applicable Hours].[Employee
Name], Employees.Exemption, Employees.[Starting Rate], Employees.[2008ETO],
Employees.HoursCarriedIn, [ETO Applicable Hours].[Hours Worked], [ETO
Applicable Hours].[ETO Taken], tblHourlyETO_Levels.Hours_Earn_Rate
FROM tblHourlyETO_Levels, Employees INNER JOIN [ETO Applicable Hours] ON
Employees.EmpNum=[ETO Applicable Hours].[Employee #]
WHERE IIf(Employees.[Starting Rate]>3,Employees.[Starting
Rate]=tblHourlyETO_Levels.Level_Number,tblHourlyETO_Levels.Level_Number=1);

The following suggestions from Clifford get me the rate and allows for
changes in rate, but chooses it as if everyone starts at level 1, and the
levels are the total hours worked rather than what you need to work at EACH
level before moving to the next.

So, if you've read this far, thank you, and do you have any suggestions?

Thanks,
Claire

 

[Claire]

Indeed, those would have been good to relay. In my attempt to keep things
easy to understand, I think I simplified too much.

As to answer your questions, here's some examples.
Employee A starting working at level one. She was accruing an hour of
vacation time for every 50 hours worked, which after the first 4,000 hours
accrued 80 hours, but over that time she used 40 hours of vacation, never
going over the 60 max hours accrued. At 4,000 hours she has 40 hours of
accrued vacation. At this point, she should automatically move up to level
two, and she should accrue 2 hours for every 50 hours worked. After she has
worked 5,000 hours, she has the 40 hours carried in from the last level
(which is 80 hours accrued - 40 hours taken), +20 hours (1,000/50*2), for a
total of 60 hours available.

If employee B started at level 1, she accrues at a rate of 1 hour for every
50 hours worked for the first 4,000 hours, 2 hours for every 50 hours worked
for the next 6,000 hours, and 3 hours for every 50 hours worked for the next
10,000 hours, and 4 hours for every 50 hours worked for any other hours
worked. If she has worked 13,000 hours she will have accrued:
(4,000/50*1)+(6,000/50*2)*(3,000/50*3)=500 hours. She will only have been
able to take all of this time as vacation if she took some along the way. At
any point during level one she can't have a balance of more than 60 hours,
during level two a balance of 120 hours, 3 more than 180, or 4 more than 240.

If Employee C started at level 3, she accrues at a rate of 3 hours for every
50 hours worked for the first 10,000 hours, and then moves to level 4,
accruing at 4 hours for every 50 hours worked. She will remain at this level
indefinitely. Over the course of 13,000 of work she would accrue
(10,000/50*3)+((13,000-10,000)/50*4)=640 hours. Because of the max
accumulated amount, she would only be able to accumulate 180 hours during the
first 10,000 hours, and then 240 hours total after that. IE, you need to
take some of your vacation in order to get all of your vacation. Hopefully
that part makes sense.

Another quirk that I think I have a possible solution to is if someone
switches from hourly to salary, or the other way around. They would still
have their accumulated hours available, and earn at the new rate, but their
time worked would have to be translated (hour to months if going from hourly
to salary, and months to hours if going from salary to hourly). For this
part I was going to have a previously worked table, which would hold the
first information, and then start counting their hours/months from the switch
onward, putting their previous worked as their baseline (what I'm using now
to pull in info from before this database). This previously worked table
would also hold information of employees who had extended periods of leave.
IE someone who was employed from 1/1/07-3/1/08, but then returned on 11/1/08.
The first information with worked hours and such would be in the previously
worked time, and the new date of employment would start off the new accruals.

Let me know if there are any other scenarios that need clarifying. The
complexity of this is what is making this more difficult to program. Too
many scenarios actually change what's going on, which is plain obnoxious.
Or, a challenge, depending on how you choose to see it.

Thanks so much for thinking about this!

Hi Claire,

That the level hours were additive would have been a good thing to
state in the initial post. And that people can start out as something other
than level one. Neither was obvious. Oh well, better late than not. But
that leads to a related question: How is the vacation accumulated when
crossing from one level to another? Say someone who started as level 1 has
5,000 hours total. How would you calculate the vacation? Likewise for
someone with 13,000 hours? How about for someone who started at level 3 and
has 13,000 hours? Any other quirks on how things work that I should know?

Clifford Bass

I've tried to reply to the following pasted post, but for some reason the
group is spiting me, and it hasn't appeared all day long. So, if something
finally kicks in and all of these appear, I deeply apologize. That said, I'm
stuck, and keep hitting a wall, so would LOVE some more help on this issue.

I'm working on accruing vacation time. There are different levels of
vacation, and an employee can move to the next level after they've worked a
number of hours at their starting level (this number of hours varies by
level). Each level also has a different rate of accrual and a max number of
hours that can be accrued. Employees start at different levels, and there
are only a certain number of levels. And to make it even more exciting,
hourly and salaried workers have different accruals (salaried depending on
how long they've worked) .

Currently I have two tables, one for hourly and one for salaried, which list
the levels, an employee table with their exemption type (hourly or salaried),
hours worked at the current level carried in (from before this database),
vacation carried in (also from before the database), and starting level,
beginning and ending dates of employment, and a table which sums the
applicable hours for accrual and hours taken off, by employee #.

The Vacation level table looks like:
Level--# Hours before next level--Earn Rate----Earn Period---Max accumulated
1----------4,000---------------------1-----------50------------60
2----------6,000---------------------2-----------50------------120
3---------10,000---------------------3-----------50------------180
4-----------n/a-----------------------4-----------50------------240


My query in progress gives me the rate for level 4+ (these levels are
static), and just lists the rest at level 1 at this point, because I can't
figure out how to change rates without insane if statements in the where.
(ETO = earned time off, ie vacation)

SELECT [ETO Applicable Hours].[Employee #], [ETO Applicable Hours].[Employee
Name], Employees.Exemption, Employees.[Starting Rate], Employees.[2008ETO],
Employees.HoursCarriedIn, [ETO Applicable Hours].[Hours Worked], [ETO
Applicable Hours].[ETO Taken], tblHourlyETO_Levels.Hours_Earn_Rate
FROM tblHourlyETO_Levels, Employees INNER JOIN [ETO Applicable Hours] ON
Employees.EmpNum=[ETO Applicable Hours].[Employee #]
WHERE IIf(Employees.[Starting Rate]>3,Employees.[Starting
Rate]=tblHourlyETO_Levels.Level_Number,tblHourlyETO_Levels.Level_Number=1);

The following suggestions from Clifford get me the rate and allows for
changes in rate, but chooses it as if everyone starts at level 1, and the
levels are the total hours worked rather than what you need to work at EACH
level before moving to the next.

So, if you've read this far, thank you, and do you have any suggestions?

Thanks,
Claire

 

[Clifford Bass]

Hi Claire,

Your examples helped clarify things. Thanks. I have pondered this for
awhile and let it percolate a bit. As you stated, "Too many scenarios
actually change what's going on...". So the direction I am leaning more and
more towards is to deal with it in a different fashion. This is by no means
completely thought out so if you follow it, you will still have lots of fine
tuning to do. It will involve some programming. I am going to use my own
table and field names because it will be easier for me to explain that way,
so you will have to adjust as needed.

I am thinking that this is a case where it is better to store the
results of the computations rather then recalculating them each time with
convoluted queries. I think I would set up a vacation table that records all
of the changes that happen. And it would keep a running total of how many
vacation hours the employee has. And maybe their current level. And maybe
their total hours and/or months worked. So you will always, only have to
look at the most recent record for any one employee to know that information.
And if you ever want to go back in history for any employee, the information
will all be there, easily pulled from the table with a simple select query.
Also, if you design it well, it can deal with both hourly and salaried
employees, including switching back and forth between type when that happens.

tblVacation_History
---------------------------
Record_ID (autonumber [sequential], primary key)
Employee_ID
Change_Date
Vacation_Level
Current_Hours_Worked (at this level)
Cumulative_Hours_Worked (at this level)
Current_Months_Worked (at this level)
Cumulative_Months_Worked (at this level)
Vacation_Change (positive for earned, negative for used)
Vacation_Balance
Comments
[other fields?]

The use of a autonumber Record_ID allows for multiple entries per day
for each employee. I would make the combination of Employee_ID and
Change_Date a non-unique index to help with queries and searching.

Add a row each day/week/month/pay period or whatever frequency is
appropriate to add in the new hours/months worked and do the computation of
the vacation balance. If the time worked crosses one of the vacation level
boundaries, do two records. One at the current level to take that level to
the maximum time worked. And another with the remaining time, at the next
level. Making the cumulative level times the remaining time. When vacation
is used, add a record with 0 for the current time worked, copy the cumulative
level time worked from the prior record, put in the vacation used as a
negative change and the new vacation balance. This design allows you to
pre-load a current information record for the employees as a starting point.
It is not dependent on employees taking leaves of absence. For each existing
employee you can give them an initial record with their current information.
When a new employee is added, a starting record would be added that has
his/her starting level and has zero for all of the other values. If an
employee shifts from hourly to salaried, you might create a new record that
takes the cumulative hours worked from the prior record, sets that to zero
and sets the cumulative months worked to the appropriate value.

Also, if you can combine the hourly and monthly vacation level rules
tables into one table that would simplify things.

tblVacation_Levels
-------------------------
Vacation_Level (primary key)
Hours_At_This_Level
Months_At_This_Level
Earn_Rate
Earn_Period
Maximum_Accumulation

or maybe

tblVacation_Levels_Alternate
---------------------------------------
Vacation_Level (first part of primary key)
Employee_Type (second part of primary key; H = hourly or S = salaried)
Units_At_This_Level (hours when Employee_Type = H; months when
Employee_Type = S)
Earn_Rate
Earn_Period
Maximum_Accumulation

The next part is all done in code (VBA). When you are ready to add in
new hours/months, open up a recordset of employees with new hours/months to
be added. Go through it row by row. For each employee get the employee's
latest record along with the appropriate vacation level information.
Something like:

select A.*, B.*
from tblVacation_History as A inner join tblVacation_Levels as B
on B.Vacation_Level = A.Vacation_Level
where Employee_ID = [the employee's ID from the recordset] and not exists
(select * from tblVacation_History as C
where C.Employee_ID = A.Employee_ID and C.Record_ID > A.Record_ID)

Then use the information in that recordset, along with the new
hours/months, to calculate any new vacation allotment and the new cumulative
information. Then create a new record in tblVacation_History that has the
results of those computations.

To do a report of the current information you would do something like
this for the record source query:

select A.Employee_ID, A.Last_Name, A.First_Name, [any other fields from A
you wish to include], B.Vacation_Level, B.Change_Date,
B.Cumulative_Hours_Worked, B.Cumulative_Months_Worked, B.Vacation_Balance,
[any other fields from B you wish to include]
from tblEmployees as A inner join tblVacation_History as B
on B.Employee_ID = A.Employee_ID
where A.Employee_Active = True and not exists
(select * from tblVacation_History as C
where C.Employee_ID = B.Employee_ID and C.Record_ID > B.Record_ID)

Hoping that is helpful and Good Luck!,

Clifford Bass