<rickcclh@gmail.comwrote in message
news:c7971e48-0356-48ea-a38b-f5519da21576@t54g2000hsg.googlegroups.com...
Quote:
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
If you want to find Rick's wage on 10/25/07:
(careful of text wrapping)
First, find the highest table entry for Rick upto, but not exceeding,
10/25/07:
dtWageDate = CDate(DMax("WageDate","tblWages","Employee='Rick' And
WageDate<=#10/25/07#"))
Then use that to read the amount on that date:
dblWageAmt = CDbl(Dlookup("WageAmt","tblWages","Employee='Rick' And
WageDate=#" & dtWageDate & "#"))
Fred Zuckerman
