Looking up Rate info based on a Date

[RocketRod]

I am developing a report that calculates cost based on, Hours Worked * the effective Rate for the date the work was completed. The Hours Worked information resides in one table and the Rate information in another. I have written code to make this work using VBA, but was wondering if this could be done with one or more queries.

The tables are structure as follows:

[B]Hours Worked[/B]
RsrcID   Hours   WorkDate
1        1       12/31/2007
1        1       1/1/2008
1        1       3/1/2008
2        1       5/1/2008
3        1       3/1/2008

[B]Rate[/B]
RsrcID   Rate  EffDate
1        10    1/1/2007
1        15    1/1/2008
1        20    5/1/2008
2        25    6/1/2008
2        30    8/1/2008
3

For RsrcID 1, I would like to return the first rate with an EffDate that is <= WorkDate

For RsrcID 2, there was error when entering the EffDate as you will notice the EffDate is greater than the WorkDate so I need to retrieve the first rate that is > the WorkDate

For RsrcID 3, there was no rate information entered. In the event of null value I would like a default rate of 30 entered, which I would handle like this NZ(Rate, 30)

I would like to write a query that would join these tables shown above with the following results:

[B]Results[/B]
RsrcID   Hours   Rate WorkDate
1        1       10   12/31/2007
1        1       15   1/1/2008
1        1       20   3/1/2008
2        1       25   5/1/2008
3        1       30   3/1/2008

Thanks in advance.

 

[ajetrumpet]

my first question for you would be: why do you have two tables for the data that you have listed? It looks like it only needs one.

 

[RocketRod]

my first question for you would be: why do you have two tables for the data that you have listed? It looks like it only needs one.

I should have mentioned the table structure is defined by an off the shelf application we are using and I only showed the columns required to solve this problem.

In addition, you would want two tables in this instance to normalize your data, otherwise a resource could only have a rate assigned after they have submitted Hours for a given WorkDate. You would also have to add the EffDate column to the Hours Worked table which would denormalize your data among other maintenance issues it would cause..