How to calculate cumulative values of query fields

method · July 12th, 2005, 08:53 AM

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 this project)
7)Projectleader ( project leader name and number that is 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 project )
11)name (Employee name and initial and last that works 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 that point.
b)cumulative value for wages given for that project task up that point.

http://i5.photobucket.com/albums/y18...ojectdata2.jpg ( query output sample)

The above query ONLY lists hours worked and wages gives for particular project task only during
each week.But i want hours worked and wages give for particle project task up to that point in week. For
example a project task might have implemented last week but not this so i want to take that in calculation as well.
I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular
project task.

Notes:

- There is a possibility that during a particular week no task been 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/y18...constraint.jpg ( pic of database)
http://i5.photobucket.com/albums/y18...oportfinal.jpg ( query output population)
http://i5.photobucket.com/albums/y18...ryindesign.jpg (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;

code for querythis week( calcualte the salary and hours worked)

Code:



SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours
      from dbo_Hourly_wages a 
      where dbo_Hours_worked.Employee = a.Employee 
      and dbo_Hours_worked.Project = a.Project 
      and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno)
                                                            from dbo_Hourly_wages b
                                                            where b.Year < dbo_Hours_worked.Year
                                                            or (b.Year = dbo_Hours_worked.Year and b.weekno <= dbo_Hours_worked.weekno))) AS Salary
FROM dbo_Hours_worked;