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 12th, 2005, 08:53 AM
Friend of Wrox
 
Join Date: Mar 2005
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to calculate cumulative values of query fields

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;





Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate values of different selectionlists vsempoux Java Basics 3 August 7th, 2008 10:25 AM
Need Cumulative Query devendar SQL Server 2005 3 March 21st, 2008 10:12 AM
Calculate a hald day in a query Brendan Bartley Access 1 January 3rd, 2008 05:55 PM
Looking Up Values In Other Fields meista Crystal Reports 0 December 11th, 2006 07:50 AM
calculate the values up crmpicco Classic ASP Basics 3 February 1st, 2005 10:18 AM





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