calculations based on date changes

  • Thread starter Thread starter W. Carroll
  • Start date Start date
W

W. Carroll

I am currently creating a database for a job cost situation. I am
first trying to create a formula that calculates a job cost (i.e.
regular hours multiplied by regular rate). In a perfect world this
would be all I need, and I know how to do this simple calculation.
However, periodically the rate changes for an employee. I need to be
able to keep the old rate for the previous records, but use the new
rate for all new inputs. I have been reading some of the other
message posts and I'm wondering if I need some sort of table to pull
my rates from. For example I need to be able to calculate if the Week
ended date is after 8/16, use the most recent pay rate. What is the
best way to handle this problem? Any information would be greatly
apprieciated.
 
For example I need to be able to calculate if the Week
ended date is after 8/16, use the most recent pay rate. What is the
best way to handle this problem?
Click to expand...

Your suggestion is quite correct: you need a historical rates table.
I'd suggest a table with EmployeeID, EffectiveDate, EndDate, and Rate;
each time an employee's rate changes you'ld add a new record to this
table.

A Non Equi Join query can be used to find the rate as of the Week
Ended:

SELECT <whatever> FROM yourtable
INNER JOIN Rates
ON yourtable.[Week Ended] >= Rates.EffectiveDate AND yourtable.[Week
Ended] < NZ([Rates].[EndDate], Date())

If the NZ function doesn't work in the JOIN clause, default EndDate to
#12/31/9999# - the largest possible date - and omit the function call.
 
Back
Top