if statement = if number is between

[JK]

I'm working on a timekeeping database and I'm struggling with the
vacation/sick time accrual part of it.

I have a table that says if an employee has been with the company for more
than one year, they begin to accrue vacation time at .833 days per month. If
the employee has been with the company more than 8 years but less than 14
years they accrue vacation time at 1.25 days per month. If the employee has
been with the company for more than 14 years but less than 20 years they
accrue at 1.667 days per month.

I have a table with employees and each employee record has their date of
hire. I can easily calculate the number of years they've been with the
company.

Where I run into trouble is when I try to take the # of years they've been
with the company a do a comparison against the Vac-Sick-Accrual table. The
vac-sick accrual table has a min years a max years and then a vaca accrual
rate and sick accrual rate for each line.

How do I get my query to compare the # of years the employee has been with
the company with the vaca-sick accrual table to come up with the # of
vacation days and sick days the employee should accrue per month?

Please help me!

Thanks

 

[Paolo]

Hi JK,
Make a query and add the table with the years the employees worked for the
company and the table with the vaca-sick accrual without join between them.
Then add the field with the worked years and put as criteria >=[min] and
<[max] from the vaca - sick table. Add also the fields with the accrual of
the vaca and sick day and the trick is done.
All this is untested (but in my mind works)

HTH Paolo