Accrual

[Claire]

I'm working on writing a query for vacation accrual, but I think there's a
simpler way to do it. I just can't think of it.

What's going on:
People have different vacation accrual levels (1-4). At each level they
accrue a certain number of vacation hours for each 50 hour period, have a
certain number of hours to work before advancing to the next level, and have
a maximum number of hours that can be accumulated. Currently I'm writing long
if statements that include all of the higher levels for each lower level, and
I'm thinking that really I should have an accrual table and that that should
create a person's current rate. I just can't figure out how to link it
appropriately.
Here's what that table would look like:

Level--# Hours before next level--#hrs for each 50 hour period-Max accumulated
1----------4,000---------------------1-------------------------60
2----------6,000---------------------2-------------------------120
3---------10,000---------------------3-------------------------180
4-----------n/a-----------------------4-------------------------240

Further fun enters because this table only holds for hourly employees.
Salaried have a similar table, but it depends on the number of months they've
worked.

Other tables include the hours each employee has worked, when they
started/ended, their starting vacation rate, and if they are salaried/hourly.
I feel like it's all there, but I'm having a problem seeing past the long
ifs.

Thanks for any help, and let me know if I can clarify any part of this.

 

[Clifford Bass]

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