T
tonyck
Hi everybody. I got a access 2000 query that lists :
1)weekno
2)year
3)project (project number )
4)QweekylyReportHeader (project description )
5)customer (customer that requested this project)
6)department (department number and name that implements thi
project)
7)Projectleader ( project leader name and number that i
responsible for this project)
8)Task (Task number that is done for this project )
9)task description (description of task )
10)employee ( employee number who is working in this projec
)
11)name (Employee name and initial and last that work
for this project )
12)hours ( number of hours employee worked in this task
==> i want cumulative for this
13)salary (amount of salary given to this employee)
===>i want cumulative for this
I want to create another query that lists :
A)cumulative value of hours worked on particular project task up tha
point.
b)cumulative value for wages given for that project task up tha
point.
http://tinyurl.com/ad3oq ( query output sample)
The above query ONLY lists hours worked and wages gives for particula
project task only during
each week.But i want hours worked and wages give for particle projec
task up to that point in week. For
example a project task might have implemented last week but not this s
i want to take that in calculation as well.
I be happy if some expert show me how i can calculate the cumulativ
value for hours worked and wages given for particular
project task.
Notes:
- There is a possibility that during a particular week no task bee
implement for particular project.
- 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
http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic o
database)
http://tinyurl.com/dur8u ( query output population)
http://i5.photobucket.com/albums/y180/method007/queryindesign.jp
(query in design view)
query that display hourly wages of certain project during each week
Code
-------------------
SELECT
querythisweek.weekno,
querythisweek.Year,
querythisweek.Project,
QweeklyReportHeader.Customer,
QweeklyReportHeader.Department,
QweeklyReportHeader.description,
QweeklyReportHeader.ProjectLeader,
querythisweek.Task,
dbo_Task.description,
querythisweek.Employee,
[lastname] & ' ' & [initials] & ' ' & [insertion] AS Name,
querythisweek.hours,
querythisweek.Salary
FROM
dbo_Task
INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task;
1)weekno
2)year
3)project (project number )
4)QweekylyReportHeader (project description )
5)customer (customer that requested this project)
6)department (department number and name that implements thi
project)
7)Projectleader ( project leader name and number that i
responsible for this project)
8)Task (Task number that is done for this project )
9)task description (description of task )
10)employee ( employee number who is working in this projec
)
11)name (Employee name and initial and last that work
for this project )
12)hours ( number of hours employee worked in this task
==> i want cumulative for this
13)salary (amount of salary given to this employee)
===>i want cumulative for this
I want to create another query that lists :
A)cumulative value of hours worked on particular project task up tha
point.
b)cumulative value for wages given for that project task up tha
point.
http://tinyurl.com/ad3oq ( query output sample)
The above query ONLY lists hours worked and wages gives for particula
project task only during
each week.But i want hours worked and wages give for particle projec
task up to that point in week. For
example a project task might have implemented last week but not this s
i want to take that in calculation as well.
I be happy if some expert show me how i can calculate the cumulativ
value for hours worked and wages given for particular
project task.
Notes:
- There is a possibility that during a particular week no task bee
implement for particular project.
- 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
http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic o
database)
http://tinyurl.com/dur8u ( query output population)
http://i5.photobucket.com/albums/y180/method007/queryindesign.jp
(query in design view)
query that display hourly wages of certain project during each week
Code
-------------------
SELECT
querythisweek.weekno,
querythisweek.Year,
querythisweek.Project,
QweeklyReportHeader.Customer,
QweeklyReportHeader.Department,
QweeklyReportHeader.description,
QweeklyReportHeader.ProjectLeader,
querythisweek.Task,
dbo_Task.description,
querythisweek.Employee,
[lastname] & ' ' & [initials] & ' ' & [insertion] AS Name,
querythisweek.hours,
querythisweek.Salary
FROM
dbo_Task
INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task;