B
Bill Jaeck
I am looking for help building an efficient query that can return the daily
costs for my employees. I have a table with my employee names and their IDs,
and another one with the daily hours worked by each employee, and finally
another table that contains the hourly rates for my employees. My hourly
rate table sometimes contains multiple rates for a single employee, and the
effective date of that rate. That's because I am nice to my employees and I
give them raises as time goes on.
I have the following tables:
Table: Employees - contains employee names and their EmployeeIDs.
EmployeeID
Name
1
Jack Jones
2
John Doe
Etc.
Etc.
Table: Hours - For each day that each Employee works, a row is written to
this table which specifies who worked when and for how many hours.
EmployeeID
Date
Hours
1
1/5/2003
2
1
2/10/2003
3
1
3/10/2003
4
2
1/6/2003
3
3
1/7/2003
3
Etc.
Table: Rates - Each Employee works for a different hourly rate, and the
rates change over time. This table contains the effective rates for each
employee and the date that the rate went into effect. The rate is in effect
for the employee until a new rate with a more recent EffectiveDate is
entered into the table.
EmployeeID
EffectiveDate
Rate
1
1/1/2003
$30.00
1
2/1/2003
$35.00
1
3/1/2003
$40.00
2
1/2/2003
$20.00
3
1/3/2003
$25.00
Etc.
Here is the problem. I want to create a "Costs" query which combines the
rows in the Hours table with the effective rate for the employee on the date
the hours were worked so that a net cost can be calculated for the work done
each day by the employee, e.g.
Query: Costs
EmployeeID
Date
Hours
Rate
Cost
1
1/5/2003
2
$30.00
$60.00
1
2/10/2003
3
$35.00
$105.00
1
3/10/2003
4
$40.00
$160.00
2
1/6/2003
3
$20.00
$60.00
3
1/7/2003
3
$25.00
$75.00
etc
My Hours table is BIG. I realize that I could write a VBA function to figure
out the rate (e.g. a function called RateForEmployee(argEmployeeID as long,
argDate as Date) as Currency). But that function has to build an SQL string
and then open a recordset for each row in my Hours table, and that takes a
long time.
Does anyone know how I could build this query without having to write a
function in VBA? I am looking for a high performance solution that somehow
joins the Hours table with the Rates table to obtain the effective rate for
the date that the hours were worked. The effective rate would be the rate
corresponding the the greatest effective date that is less than or equal to
the date that the hours were worked.
costs for my employees. I have a table with my employee names and their IDs,
and another one with the daily hours worked by each employee, and finally
another table that contains the hourly rates for my employees. My hourly
rate table sometimes contains multiple rates for a single employee, and the
effective date of that rate. That's because I am nice to my employees and I
give them raises as time goes on.
I have the following tables:
Table: Employees - contains employee names and their EmployeeIDs.
EmployeeID
Name
1
Jack Jones
2
John Doe
Etc.
Etc.
Table: Hours - For each day that each Employee works, a row is written to
this table which specifies who worked when and for how many hours.
EmployeeID
Date
Hours
1
1/5/2003
2
1
2/10/2003
3
1
3/10/2003
4
2
1/6/2003
3
3
1/7/2003
3
Etc.
Table: Rates - Each Employee works for a different hourly rate, and the
rates change over time. This table contains the effective rates for each
employee and the date that the rate went into effect. The rate is in effect
for the employee until a new rate with a more recent EffectiveDate is
entered into the table.
EmployeeID
EffectiveDate
Rate
1
1/1/2003
$30.00
1
2/1/2003
$35.00
1
3/1/2003
$40.00
2
1/2/2003
$20.00
3
1/3/2003
$25.00
Etc.
Here is the problem. I want to create a "Costs" query which combines the
rows in the Hours table with the effective rate for the employee on the date
the hours were worked so that a net cost can be calculated for the work done
each day by the employee, e.g.
Query: Costs
EmployeeID
Date
Hours
Rate
Cost
1
1/5/2003
2
$30.00
$60.00
1
2/10/2003
3
$35.00
$105.00
1
3/10/2003
4
$40.00
$160.00
2
1/6/2003
3
$20.00
$60.00
3
1/7/2003
3
$25.00
$75.00
etc
My Hours table is BIG. I realize that I could write a VBA function to figure
out the rate (e.g. a function called RateForEmployee(argEmployeeID as long,
argDate as Date) as Currency). But that function has to build an SQL string
and then open a recordset for each row in my Hours table, and that takes a
long time.
Does anyone know how I could build this query without having to write a
function in VBA? I am looking for a high performance solution that somehow
joins the Hours table with the Rates table to obtain the effective rate for
the date that the hours were worked. The effective rate would be the rate
corresponding the the greatest effective date that is less than or equal to
the date that the hours were worked.