How to get the most recent value from a table column?


Hi every body. I want create a query that has the following fields on
it:

Project : project number
Year: year that project is carried out
Weekno: week no that project is carried out
Task: Task number that is done for this project
Employee: employee number that did this task
Amount: amount of salary given for this employee for this particular
project

I used access query designer and selected the db_hours_worked table and
dbo_hourly_wages I joined the employee fields together and project
fields together. But I cannot join weekno in both tables together since
an employee
can have different salary in different weeks!

Amount is calculated by multiplying the number of hours worked during
this week multiplied by most recent salary of that employee for the
particular project. I be happy if some one show me how I can get the
most recent salary of employee from dbo_Hourly_wages table then
multiply it by number of hours worked this week and put it in amount
column. For the current population of db_hourly_wages as u can see in
the picture posted the amount that I want to use for amount calculation
is 40.

I managed to write part of the query but it does not out put a result
and also it does not calculate the salary amount. Thanks

- One employee can work in more then one project
- One employee can have more then one salary (amount) for the same
project because he might get raise in salary!
- Only tasks carried this week will be printed in the weekly report



Code:
--------------------


SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hourly_wages.amount
FROM dbo_Hours_worked INNER JOIN dbo_Hourly_wages ON (dbo_Hours_worked.Employee = dbo_Hourly_wages.Employee) AND (dbo_Hours_worked.Project = dbo_Hourly_wages.Project);


--------------------

http://i5.photobucket.com/albums/y180/method007/constraint.jpg
==>pic of database

http://tinyurl.com/887wl
===> pic of hours wages table
http://i5.photobucket.com/albums/y180/method007/reporterror1.jpg
====> pic of query in design view
http://i5.photobucket.com/albums/y180/method007/queryresult.jpg
====pic of query result


--
tonyckPosted from http://www.pcreview.co.uk/ newsgroup access

.

Relevant Pages

  • Re: Paul Thomas hears, but doesnt get it
    ... > the view of any particular employee - that is, ... > assuming that the matching contributions are intended to vest in at least ... > the power under a plan that is in fact qualified at the time of election, ... > the separate limitations on the maximum elective deferral amount). ...
    (misc.taxes)
  • Re: Running Total
    ... >How do can you write a query to show a running total. ... >employee, date, amount how would I get a additional column to show the ... Prev by Date: ...
    (microsoft.public.access.queries)
  • Re: Re:The lawyers statement regarding my tax-on-nothing
    ... > In other words, if I can paraphrase, the VCR assumed that the amount by ... > amount contributed by the employee rather than by the employer (i.e., if, ... > income for the year of distribution). ...
    (misc.taxes)
  • Re: more help with skipping blank lines
    ... amount of code) is to use a subreport for the column listing of benefits. ... >>I have a report that lists various employee benefits. ... When the graph is there, ...
    (microsoft.public.access.reports)
  • skipping blank lines - again
    ... If the amount is displayed in it's own ... >> I have a report that lists various employee benefits. ... When the graph is there, ...
    (microsoft.public.access.reports)