SEARCH YOUR SOLUTION HERE  

How to calculate cumulative values of this 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/y180/method007/weeklyprojectdata2.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/y180/method007/constraint.jpg ( pic of database)
http://i5.photobucket.com/albums/y180/method007/hourlywagesroportfinal.jpg ( query output population)
http://i5.photobucket.com/albums/y180/method007/queryindesign.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

Posted On: Monday 22nd of October 2012 05:50:13 AM Total Views:  375
View Complete with Replies




Related Messages:

Format date in a calculated field   (325 Views)
I have a label control for a subform, which is a concatenation of fields derived from data in another subform. It'd be better if it reads as: "John Smith -- Start Date: Sunday, August 15, 2010;" but it comes out as: "John Smith -- Start Date: 8/15/2010." Is there a way to force the formatting of the date
Keeping track of report values   (234 Views)
Nevermind.
Unique values on similar but different records   (193 Views)
This may be a simplistic question but I'm having some diffuculty figuring it out. I have multiple rows of data where I need to use the "Unique Values" property in my query to limit only what I need. However, I have a "time" field and their are similar times on different records. So say I have a 100 records but 8 of them have the same "time" value, how can I use someting like the "Unique Values" property to get my rows down from several hundred to 100, but not lose the 8 similar rows and end up with 92 rows, when I have to turn around and sum these times and need all the times to be there Maybe I'm approaching it the wrong way and should be doing a SUM of "times" to begin with for that field, but I'm not being able to get that to work either. Any suggestions
Replace parameter values in control source property   (152 Views)
I have a text box in a report, with it's control source property set to =[BegDt]. Upon loading the report a message box pops up and asks the user for the value of [begDt]. This occurs 3 more times for 3 other parameters. Now I want to change the design of the report so I decided to put these 4 parameters on a form so the user can fill in the 4 values all at once rather than responding to 4 message boxes that pop up when you run the report. The problem is now the control source properties in all the different text boxes need to be changed to [forms]![frmABC]![txtBegDt] etc.etc. There are over 100 text boxes in this report that make reference to the 4 different parameters. Is there a replace command I can use to change all the occurrences in the text boxes or perhaps a simpler way would be to pass the values to the report through code. I tried placing this in the report header's format event and it doesn't seem to have any effect. Code: Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) begytd = [Forms]![frmABC]![txtStDt] endytd = [Forms]![frmABC]![txtEndDt] curmo = [Forms]![frmABC]![cboMo] curyr = [Forms]![frmABC]![cboYr] End Sub I should mention that the report uses a table (tblRecap) as it's record source. I tried using a query that refers back to the form for the parameter values but that means I have to use the query as the reports record source and that creates a problem because the report needs fields from the table to perform calculations. Can someone help me with a solution please.
Select only a Certain data to calculate in report   (159 Views)
I have a database that contained the following fields. VacEarned,VacUsed,VacPlan (Yes,No). In my report, I grouped VacPlan field (which is yes/no field) together and sum it up by VacUsed. However, I'd like to put another formula in my report to subtract the the Sum of the VacPlan from VacEarned...I have such a hard time doing that..Is there way that I can put if statement in the report option that would do the following: If VacPlan is Yes, than Sum VacUsed and use it to subtract from VacEarned..is this possible.. I hope this make sense...
Selecting lowest values in a query?   (142 Views)
Hi all - I have a table that contains Client ID, Supplier Name, Order Number, Order Date, and Price. Example - Client ID -- Supplier Name -- Order Number -- Order Date -- Price 1111111 Supp A 222222 1/2/06 1,000 1111111 Supp B 222222 1/2/06 2,000 1111111 Supp C 222222 1/2/06 4,000 1111111 Supp D 222222 1/2/06 500 1111111 Supp E 222222 1/2/06 1,200 I need to determine the 2 lowest prices per order number. In this case that would be Supp D - 500, and Supp A - 1000. I am using the Top function and sorting the price in ascending order to get the lowest two prices. The problem is that it selecting the lowest 2 prices of all the orders on the table, not each individual order. I tried grouping on the other fields, but still came up with incorrect results. Any ideas
Null values and Like statements   (170 Views)
I am using a query with Like statements that looks at a field in a form. The form defaults to "*" so that all of the records will show. You can search with the normal wildcards. The problem I have is that the records containing Null values don't appear. I was trying to see if I could use an IIF statement in the criteria of the query, but have had difficulty. What is the proper way to have all records show if * is the default, but only the specific criteria if something is entered using wildcards
default values   (142 Views)
I have a form that when the user clicks a button a record is transferred to a new table/form. In the new form there are some extra fields that need to have default values in them. I tried setting the default values in the properties menu but that only works when creating a new record. Is there a way to have default values come up on a transferred record
Show label instead of values   (129 Views)
I have lookup table for one or more fields in my table. On a form or in a table, I don't want to see a bunch of values for a field, as I don't know what they are until I have to go to the drop down arrow and see the label. For example: My table and its field looks like this: Notes_Category 3 2 3 2 3 2 I would rather see the label in he fields but still have the combo box....help anyone
Unique values from table   (136 Views)
I have a table that has a field Support_ID which has duplicate records for many of the Support_ID's....This table is going to be a reference table for other tables and as such I want to get the table into a format that there is only one record for each Support_ID.. I tried to create a query through QUERY DESIGN....I added the table and added just the Support_ID and click the total sum button in the QUERY DESIGN TOOLs....I get a unique representation of the records. BUT i want all the data in the table. I add all the fields in the table to the query and I get all the values returned. I then change the total row in the query to "First" and I still get all the records.... How do I get rid of all the duplicate records based on this one field but retain all the other data. I can attach the table if need be
Coloring negative values in a field   (144 Views)
Is there any way I can highlight only negative values in a Report I have a report with basically three fields i.e. Sent, Received and Variance in which the variance can sometimes either be a positive (excess) or negative (shortage) if it is Zero (Balanced). My Boss wants all the shortages highlighted in red on this report. I'm looking at possibilities of using the OnOpen event procedure but the problem is I'm completely blank on how I can make this work. I'm using office 2003.
Passing values from one form to another   (253 Views)
! I have 2 forms, frm1 and frm2. At frm1 i use a sample code at afterupdate of a combobox, like: Dim FormOpemArgs$, TargetTextBoxName$ 'code for TargetTextBoxName FormOpemArgs = TargetTextBoxName & ";" & Me.lastname strForname = Choose(Me.OptWeekDays, "MON", "TUS", "WED", "THU") DoCmd.OpenForm strForname, , , , , , FormOpemArgs So, after the update of the combobox, the selected form opens(i.e. MON form) and it passes the values lastname and TargetTextBoxName at the new form through FormOpemArgs variable. At the onload event of form MON i have this: Private Sub Form_Load() Dim MyValues() As String If Not IsNull(Me.OpenArgs) Then MyValues = Split(Me.OpenArgs, ";") Me.txtArgs1 = MyValues(0) Me.txtArgs2 = MyValues(1) End If End Sub So, my job is done. My problem is that at the first form - where the combobox is, i have more than one comboboxes (continuous form). So, all i want is every time i update a combobox at a record of continuous form, to reload the form MON for example, so i can pass the new values at this form. How can i do this Thank you in advance!
Insert null values for date in ms access 2003   (193 Views)
how to allow null value to be added into database (access 2003) The date changed to 11/30/1999 whenever there is null.
Subform control source from calculated textboxes   (174 Views)
I have what seems to be a simple problem but I am having trouble coming up with the solution. I have a subform (continous) with 3 textboxes: Qty, Price, Total. Qty and Price are from a table. I want the Total textbox source to be Qty*Price, thus not having to store a calculated field. I tried setting the source to Code: [Unit_Price]*[Quantity] but that gets me #Name. Is there a simple step I am missing here
Passing values to queries by form   (170 Views)
In my Coin Inventory database there is a category of books that each coin can be housed in. One coin will be stored in one of the books. I created a form (frmSelectFolder) that has a list box (lstSelectFolder) that the user makes a selection to determine which book they want to edit or view. The command button to call the form is disabled so the user is forced to either exit the form frmSelectFolder or make a selection from the list box. Once the user makes a selection the command button is enabled allowing the user to click it. I am calling the form frmSelectFolder from a menu (its a popup box) and the form closes after the command button is clicked. This is what I have working now. What I want to happen is that the command button opens up a form (frmFolderEdit) and the value of StorageID (from the query qryStorage underpinning the form frmSelectFolder) is passed to the frmFolderEdits query qryFolderEdit.. How do I go about doing this
Testing values   (173 Views)
I have two textboxes on a form. I want to test to see if they have values. If text1 has a value write it to a variableX If text1 does not have a variable then test text2 If text2 has a variable then write it to a variableX If text2 does not have a variable then clear the variable. Any thoughts on how to do this THanks
AfterUpdate - values not calculating   (212 Views)
Below is the code I have created to calculate the values "CalcValue" and "CalcWork". The values for AssignValue, AssignPriority, Complex, Effort, Goal are all assigned by the selection made in the referred to/related combo boxes. However, the caluclated values return the concatonation of the intergers and not the sum of the integers. Additionally, if I am to use the CDec() function how do I specify the number of decimal places what am I missing here thanks! Private Sub cboAssignPriority_AfterUpdate() Dim CalcValue, AssignValue, AssignPriority, Complex, Effort, Goal, CalcWork As Integer Value = 0 AssignValue = 0 AssignPriority = 0 CalcValue = 0 CalcWork = 0 AssignValue = Me!cboAssignValue.Column(3) AssignPriority = Me!cboAssignPriority.Column(2) Complex = cboDBObjectID.Column(2) Effort = cboTaskTypeID.Column(3) Goal = cboAgencyGoalID.Column(2) Value = AssignValue + AssignPriority CalcWork = Complex + Effort + Goal CalcValue = CDec(Value) CalcValue = Me!lngCalPriority Debug.Print "Complex="; cboDBObjectID.Column(2) Debug.Print "Effort="; cboTaskTypeID.Column(3) Debug.Print "Goal="; cboAgencyGoalID.Column(2) Debug.Print "AssignValue.Column(3)="; cboAssignValue.Column(3) Debug.Print "AssignPriority.Column(2)="; cboAssignPriority.Column(2) Debug.Print "Value ="; Value Debug.Print "CalcValue ="; CalcValue Debug.Print "CalcWork ="; CalcWork End Sub , thanks - I think that's what I've done (almost) but... CalcValue won't calculate correctly! I don't get it. jh Originally Posted by sbenj69 Try something like this: Code: Private Sub cboAssignPriority_AfterUpdate() Dim MyValue as Single Dim CalcValue as Single Dim AssignValue as Single Dim AssignPriority as Single Dim Complex as Single Dim Effort as Single Dim Goal as Single Dim CalcWork as Single MyValue = 0 AssignValue = 0 AssignPriority = 0 CalcValue = 0 CalcWork = 0 AssignValue = Val(Me!cboAssignValue.Column(3)) AssignPriority = Val(Me!cboAssignPriority.Column(2)) Complex = Val(cboDBObjectID.Column(2)) Effort = Val(cboTaskTypeID.Column(3)) Goal = Val(cboAgencyGoalID.Column(2)) MyValue = AssignValue + AssignPriority CalcWork = Complex + Effort + Goal CalcValue = Format(MyValue,"0.00") CalcValue = Val(Me!lngCalPriority) Debug.Print "Complex="; Val(cboDBObjectID.Column(2)) Debug.Print "Effort="; Val(cboTaskTypeID.Column(3)) Debug.Print "Goal="; Val(cboAgencyGoalID.Column(2)) Debug.Print "AssignValue.Column(3)="; Val(cboAssignValue.Column(3)) Debug.Print "AssignPriority.Column(2)="; Val(cboAssignPriority.Column(2)) Debug.Print "Value ="; MyValue Debug.Print "CalcValue ="; CalcValue Debug.Print "CalcWork ="; CalcWork End Sub Not sure if you meant for these lines to be like this: Code: CalcValue = Format(MyValue,"0.00") CalcValue = Val(Me!lngCalPriority) Not exactly sure if that's what you wanted, just went off the code you had posted.
IIf Statements and NULL values   (210 Views)
Within my query I have a field that I need to do a nested IIf Statement that has a NULL value. The name of the field is "smokingcessation". The possible responses are a "1" = Yes, "2"= No, "3"=Unknown and there is also the possibility of a "no answer" at all (blank). I've got the following statement to work so far but need to include the possibility of a blank/no answer response. My statement so far is as follows and is working: IIf([smokingcessation]=1,"Yes",IIf([smokingcessation]=2,"No","Unknown")) How would I do a statement that would take into account the existing possibilities plus that of a blank (NULL) answer and leave the field blank in that case In otherwords, I need to see a "Yes", "No", "Unknown" or a blank field.
Default values   (125 Views)
On a form I have text box named ImageVersion, and it has a default value of AO-02.17.06. It is likely that the image version's will be updated, it would be helpful if the user could change the default value currently set. Right now, I can do this temporarily, but it does not stay once the form is closed. If the form is closed, then opened, it reverts to the original default value set. I am not using a split database. I have it setup on a server, and users access it through there. Any suggestions Jared
unbound report - setting control values   (124 Views)
I have a very simple report - just one field. I have a Form - "Cases". The button to open the report is on that form as are the values I want in the report. I want to pull the value of Case Name and Case Number from the current form view and put them together in the single field on my report. I can "almost" do it. Then VBA gives me a warning - it says that you can't assign a value to the control on my report. Can anyone give me any ideas by the way: I chose to make this an unbound report because I'm using SQL server as my back-end and my Access is an .adp file. SQL (so I was told by the programmers at work) can't pull in variable criteria from a form like Access Queries can. So this all has to be put in VBA.