By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Choosing a field's record based on date

P: n/a
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
Mar 14 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

<ri******@gmail.comwrote in message
news:c7**********************************@t54g2000 hsg.googlegroups.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
Mar 14 '08 #2

P: n/a
<ri******@gmail.comwrote in message
news:c7**********************************@t54g2000 hsg.googlegroups.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("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
Mar 14 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.