T
tonyck
Hi every body. I want create a query that has the following fields o
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 particula
project
I used access query designer and selected the db_hours_worked table an
dbo_hourly_wages I joined the employee fields together and projec
fields together. But I cannot join weekno in both tables together sinc
an employee
can have different salary in different weeks!
Amount is calculated by multiplying the number of hours worked durin
this week multiplied by most recent salary of that employee for th
particular project. I be happy if some one show me how I can get th
most recent salary of employee from dbo_Hourly_wages table the
multiply it by number of hours worked this week and put it in amoun
column. For the current population of db_hourly_wages as u can see i
the picture posted the amount that I want to use for amount calculatio
is 40.
I managed to write part of the query but it does not out put a resul
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 resul
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 particula
project
I used access query designer and selected the db_hours_worked table an
dbo_hourly_wages I joined the employee fields together and projec
fields together. But I cannot join weekno in both tables together sinc
an employee
can have different salary in different weeks!
Amount is calculated by multiplying the number of hours worked durin
this week multiplied by most recent salary of that employee for th
particular project. I be happy if some one show me how I can get th
most recent salary of employee from dbo_Hourly_wages table the
multiply it by number of hours worked this week and put it in amoun
column. For the current population of db_hourly_wages as u can see i
the picture posted the amount that I want to use for amount calculatio
is 40.
I managed to write part of the query but it does not out put a resul
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 resul