Picking an entry by date

[rickcclh]

Hi. I am newish to Access and have a question. My friends familiar
with Access don't know the answer to this one either. I feel like it
is just on the tip of my brain but I can't get it.

How can I select an item in a table based on the date? For example,
an employee may have many hourly wages listed by date of hire and
subsequent raises.

How do I pick the right wage based on a given date? (Looking back at
labor reports, I want to solve this question: On this day the
employee worked 10.5 hours. What did he earn?)

I have an employee list table and a linked wages table that shows wage
and effective date of any new wage:
Rick, 5/5/07, $12.50
Rick, 9/5/07, $13.25
Rick, 2/15/08, $15.00

How do I have Access, or a querry or whatever choose which wage to
apply for a given date. Say, show the correct wage for 5-1-07,
10/25/07, and 3/2/08. Ultimately I want to run a query that will say:

Date, employee, hours worked, wage, pay
10/25/07, Rick, 10.5, $13.25,
$139.13

Any help and however you want to show me is great. If it can be done
in an SQL statement, a logical expression, or QBE example - I'll take
anything!

Thanks in advance!
Rick

 

[Allen Browne]

See Tom Ellison's article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html