Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 3rd, 2005, 03:32 PM
Friend of Wrox
 
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to get the most recent value from a 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/y18...constraint.jpg
            ==>pic of database

http://i5.photobucket.com/albums/y18...umofamount.jpg
            ===> pic of hours wages table
http://i5.photobucket.com/albums/y18...porterror1.jpg
            ====> pic of query in design view
http://i5.photobucket.com/albums/y18...ueryresult.jpg
            ====pic of query result





Similar Threads
Thread Thread Starter Forum Replies Last Post
getobject() failure- recent Loralee Access 2 November 24th, 2006 02:29 PM
Most Recent Date Ludgero SQL Server ASP 1 August 29th, 2006 12:48 PM
Most recent list bmains Forum and Wrox.com Feedback 3 June 29th, 2004 07:38 AM
Most recent ID Colonel Angus SQL Server 2000 6 March 31st, 2004 09:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.