Choosing a field's record based on date

[rickcclh@gmail.com]

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]

Re: Choosing a field's record based on date

See Tom Ellison's article:
Lookup in a range of values in a query
at:

http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<rickcclh@gmail .comwrote in message
news:c7971e48-0356-48ea-a38b-f5519da21576@t5 4g2000hsg.googl egroups.com...

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

[Fred Zuckerman]

Re: Choosing a field's record based on date

<rickcclh@gmail .comwrote in message
news:c7971e48-0356-48ea-a38b-f5519da21576@t5 4g2000hsg.googl egroups.com...

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("Wag eDate","tblWage s","Employee='R ick' And
WageDate<=#10/25/07#"))

Then use that to read the amount on that date:
dblWageAmt = CDbl(Dlookup("W ageAmt","tblWag es","Employee=' Rick' And
WageDate=#" & dtWageDate & "#"))

Fred Zuckerman