A
Ann Scharpf
The government agency where I work is changing the way it tracks labor cost.
We used to use a flat hourly rate for every employee. Now we are going to
use each employee's actual wage rate. Each person MAY receive a "step
increase" on their anniversary date which is, of course, variable.
I have set up two tables:
GovtEmployeeGradeStep
Overtime
(> indicates key fields)
Obviously, I link the two tables by Grade & Step.
I'm having trouble figuring out how to get my query to select the right
employee record to find the appropriate Base rate. EVERY employee will have
a record with an EffectiveDate=10/1/2009 (the first day of the fiscal year).
SOME employees may have an additional record. I was thinking of something
like the Pay Period Ending date >= EffectiveDate then use that rate. The
problem is the PPE date could well be > BOTH records that the employee has in
GovtEmployeeGradeStep.
For example
Employee = 123456
EffectiveDate = 10/1/2009
Grade = 12
Step = 4
Employee = 123456
EffectiveDate = 2/15/2010
Grade = 12
Step = 5
How do I get the cost calculation for PPE 12/19/09 to use Grade 12, Step 4
.... But the cost calculation for PPE 3/27/10 to use Grade 12, Step 5?
I hope my question makes sense. And I'm using Access 2003 if that makes a
difference. As always, thanks for your help!
We used to use a flat hourly rate for every employee. Now we are going to
use each employee's actual wage rate. Each person MAY receive a "step
increase" on their anniversary date which is, of course, variable.
I have set up two tables:
GovtEmployeeGradeStep
BaseEmployee
EffectiveDate Grade
Step
GovtWageBaseOvertime
Grade
Step
Overtime
(> indicates key fields)
Obviously, I link the two tables by Grade & Step.
I'm having trouble figuring out how to get my query to select the right
employee record to find the appropriate Base rate. EVERY employee will have
a record with an EffectiveDate=10/1/2009 (the first day of the fiscal year).
SOME employees may have an additional record. I was thinking of something
like the Pay Period Ending date >= EffectiveDate then use that rate. The
problem is the PPE date could well be > BOTH records that the employee has in
GovtEmployeeGradeStep.
For example
Employee = 123456
EffectiveDate = 10/1/2009
Grade = 12
Step = 4
Employee = 123456
EffectiveDate = 2/15/2010
Grade = 12
Step = 5
How do I get the cost calculation for PPE 12/19/09 to use Grade 12, Step 4
.... But the cost calculation for PPE 3/27/10 to use Grade 12, Step 5?
I hope my question makes sense. And I'm using Access 2003 if that makes a
difference. As always, thanks for your help!