|
Subject:
|
How to get the most recent value from a column
|
|
Posted By:
|
method
|
Post Date:
|
7/3/2005 3:32:54 PM
|
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
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://i5.photobucket.com/albums/y180/method007/reporterror2maximumofamount.jpg
===> 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
|
|
