Queries :: Calculate Cumulative Values Stored In Field
Oct 7, 2013
I have several projects with different tasks for each. I have 3 fields [fkProjectsID], [TaskOrder] and [Duration] in a table for project tracking with that structure:
[fkProjectsID] [TaskOrder] [Duration]
1 /1 /5
1 /2 /8
1 /3 /15
1 /4 /6
2 /1 /8
2 /2 /30
2 /3 /25
I want to calculate cumulative values stored in [Duration] field (represent a number of days). I'm using the field [TaskOrder] to order different tasks within each project. With some testing, I was able to calculate cumulative [Duration] with 1 project using the DSum fucntion as following:
CumulDuration: DSum("[Duration]", "[tblProjectTracking]", "[TaskOrder]<=" & [TaskOrder])
I was having the sequence: 5, 13, 28, 34 for respectively Task 1,2,3,4. However, when I add a second project (and then a third...), I need to be able to filter based on [fkProjectsID] as well (i.e. a specific DSum by ProjectsID). I add this criteria but I get the sum of [Duration] on each row instead of the cumulative:
CumulDuration: DSum("[Duration]","[tblProjectTracking]","[TaskOrder]<=" & [TaskOrder] And "[fkProjectsID]=" & [fkProjectsID])
View Replies
ADVERTISEMENT
Jul 12, 2005
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
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)
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;
View 5 Replies
View Related
Feb 22, 2014
I have a table where there are multiple vehicles, each identified by their vehiclenumber. Each record holds the vehiclenumber, date and odometer reading. I need to figure out how to calculate records in this table per each vehiclenumber.
Below is a code that works, but only when i have each vehicle with the same vehiclenumber.
SELECT tblOdometer.VehicleNum, tblOdometer.ODate, tblOdometer.Odometer, tblOdometer.Odometer AS OdomAlias,
Nz(DLast("Odometer","tblOdometer","[Odometer] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference
FROM tblOdometer;
View 4 Replies
View Related
Sep 25, 2015
I have developed a database but have had difficulty with sorting data within subform of a main form. The subform displays the related tasks that correspond with main form that has been selected. Within the main form I have also created a text field that defines a particular sorting sequence of the tasks found within the subform which is titled Task Sequence. What I would like to do, is use the Task Sequence field to sort the order of tasks within subform. I have tried: IIf([ID] Is Null, 0, Val([Task Sequence])) within the Advanced filter/sort but either it shows only the first task defined in the Task Sequence or it wants to filter the main form and not the subform.
View 14 Replies
View Related
Apr 13, 2014
I am using a predefined query to define the sql statement that will serve as recordsource to a form. Syntax is fine. The predefined query does not have the primary key of the table. The selection in the recordsource is done using this key. So I add this field to the design of the query. I save the query. It says the query is saved. But it is never saved. Run the query again it is not changed.
View 5 Replies
View Related
Jan 9, 2007
Hi,
I have a query that brings back data as follows (sample):
1 30/5/2006 £100,000
2 30/6/2006 £150,000
3 30/7/2006 £250,000
The currency values are all cumulative - is there a way to add a calculated column to calculate the movement using the previous record using SQL? i.e.
Item Date cumValue Movement
1 30/5/2006 £100,000 £100,000
2 30/6/2006 £150,000 £50,000
3 30/7/2006 £250,000 £100,000
Any help greatly appreciated.
Regards,
Simon
View 4 Replies
View Related
Apr 30, 2008
Hi I have a table that looks like this
ordered_equip--------------2008------------2009-----------2010
itemCode1-------------------0----------------1--------------0
itemCode2-------------------0----------------2--------------1
itemCode3-------------------0----------------2--------------1
As you can see in the year 2010 items 2 and 3 go down from qty 2 to 1. What I am trying to do is to keep track of everything that was ever shipped to the customer. So with that in mind the above table is showing that Qty-2 was ordered in 2009 and Qty-1 was ordered in 2010. I want to add these as I go along. So my desired table would look like the following
ordered_equip--------------2008------------2009-----------2010
itemCode1-------------------0----------------1--------------1
itemCode2-------------------0----------------2--------------3
itemCode3-------------------0----------------2--------------3
in this table 2010 shows Qty-3 which means 2 was present on site in year 2009 and 1 more was added in 2010 to make the qty 3. I want to write a storedProcedure or something similar to convert the first table into the second table. I said storedProcedure because I am used to doing this in SQL Server.
View 4 Replies
View Related
Mar 13, 2013
I have a table with a list of different government programs that products can take advantage of. Each of these programs has criteria such as "must use less than 1000W" or "lasts for 100 hours". Rather than have a column for each possible condition, I've created 3 fields that will accept any type of condition; Var1Condition (example: watts) , Var1Requirement (example: >=), and Var1Value (example: 50). I figured this would be the more efficient database design than to add 15-20 columns.
I then built a form that would where I could enter product attributes and would hopefully query my database and only return the programs for which the product would qualify. So I would have a field named "Watts" in which I would put the wattage of the product and then I would see which programs it would fall in.
In theory, it should be simple. I figured I could just find a way to combine my 3 criteria fields into one string "Forms!Search!watts >= 50" and then use that as a query filter, but I can't find a way to do it.
View 2 Replies
View Related
Aug 10, 2015
How to set up my tables as I'm just starting off with setting my database up.
I'm doing a simple database to track the purchase orders (PO) I am managing. Each PO has a PO Number and an Original Value. POs may have multiple amendments which would change the PO value. I would however like to keep the history of the PO original value and all different amendments.
So I created two tables:
tblPO:
ID
PO Number (Number)
PO Original Value (Currency)
PO Sum of Amendments (???????????)
PO Current Value (Calculated = PO Original - PO Sum of Amendments)
tblPOAmendments:
ID
PO (Lookup from tblPO)
PO Amended Value (Currency)
Amendment Date (Date/Time)
Amendment Desc (Text)
Now the two, million dollar questions are:
1.) Is this the right table structure to use.
2.) How do I go about calculating the Sum of Amendments field?
View 4 Replies
View Related
Dec 23, 2013
Why is this update query not working, I'm trying to update a date field stored in a table.
The new date is passed from a txtbox on a form to the update query!!!
SQL code
UPDATE TblDietPlantemp SET TblDietPlantemp.MealDate = [Forms]![FrmSwitchBoard]![txtCusDate];
View 3 Replies
View Related
Aug 22, 2013
how can i do this in a query
Id---Q---Cu
1---10---10
1---10---20
1---10---30
2---10---10
2---10---20
2---10---30
View 2 Replies
View Related
Aug 20, 2013
queries cumulative reduction, basically i have 2 tables, with the follow data
stock table
idProduct
QuantityStock
[Code]....
View 4 Replies
View Related
Nov 27, 2013
I have a dataset like this
ABCDEFG
1.AccDateStartIntWLMV
2.SSAAB15028815/02/88Month1.25101000
3.SSAAB18028818/02/88Month020980
4.SSAAB25028825/02/88Month1.2510970
5.SSAAB29028829/02/88Month010960
6.SSXXB15028815/02/88Start 1.25101000
7.SSXXB18028818/02/88Month020980
8.SSXXB25028825/02/88Month2.2510970
9.SSXXB29028829/02/88Month010960
I used to to apply this logic in XL to do my job A-F = XL Columns & 1-9 XL Rows
ifA2=A1, E2+E1, E2
This is to have a cumulative figure for a month. Just bcoz the data is growing in large numbers, we were advised to use access for which I don't know how to perform this calculation.
View 1 Replies
View Related
Feb 12, 2008
Hi,
I have combo boxes on a form which are pulling values from a one table and being stored in another table. However they are being stored as '1' or '2' in the other table once selected on the form, as instead of being stored as their literal values ie 'car' or 'van'. Is there any way of making this happen, as it makes reporting a nightmare! Thanks in advance...
View 2 Replies
View Related
Aug 4, 2005
Hi,
I am calculating two fields (qtyorder-qtydesp) in a stored procedure.
The second field is from another query (view) which may not have a record relating to the main record (stored procedure).
The problem is the second field is simply left blank but I need it to be 0.
It makes the calculation in turn result in a blank field, i.e. 1-0 should equal 1 but because of the blank field it returns a null value.
I found that the good old Nz function wont work with stored procedures, is there any alternative?
Thanks,
James
View 5 Replies
View Related
Feb 12, 2008
Hi,
I have combo boxes on a form which are pulling values from a one table and being stored in another table. However they are being stored as '1' or '2' in the other table once selected on the form, as instead of being stored as their literal values ie 'car' or 'van'. Is there any way of making this happen, as it makes reporting a nightmare! Thanks in advance...
View 1 Replies
View Related
Aug 4, 2005
i am trying to get a table to automaticaly calculate numbers entered into a field, for example field 3 allready has the number 10 inside it, if i then enter the number 15 i want the field to calculate the existing number with the new number and display 25?
currently when i update the field then i will see 15, so i need to tell it to do something else and that is where i am stuck!
please help.
Bev :confused:
View 4 Replies
View Related
May 26, 2013
I want to know the "cummulative sum of a calculated field".
Given Lookup table : A = U, V, W | B = X, Y, Z
Field C = iif([A]="U",10, iif([A]="V",20, iif([A]="W",30, 0)))
Field D = iif([B]="X",10, iif([B]="Y",20, iif([B]="Z",30, 0)))
Field E = C+D
Field F = Cummulative sum of "Field E". What would be the expression for the cummulative sum here ?
---------------------------------------------------------------------------------
I tried F = Sum(C+D), but it shows me error
---------------------------------------------------------------------------------
View 2 Replies
View Related
Mar 28, 2014
I am looking for a way to get a progressive cumulative total from daily entries on Odometer records from multiple vehicles. (My current SQL query is not working) I will eventually run a report from these between two dates. I found the thread here, but am unsure of how to implement this - if this is indeed what I need in lieu of my current code..?
[URL]
My current problem is that, not all my previous entries are correct; Im not sure what is happening to the numbers about halfway through...
For clarification, here is my current code and an example of what is needed. The issue is highlighted in yellow. Excel shows the correct calculation
Code:
SELECT qry_ODO_Table.ID AS OdomAlias, qry_ODO_Table.ODate, qry_ODO_Table.VehicleNum, Nz(DFirst("Odometer","qry_ODO_Table"),0) AS StartOD, Nz(DLast("Odometer","qry_ODO_Table","[ID] < " & [OdomAlias]),0) AS Previous, qry_ODO_Table.Odometer, [Odometer]-[Previous] AS Difference
FROM qry_ODO_Table
ORDER BY qry_ODO_Table.ID;
View 12 Replies
View Related
Mar 2, 2015
I have two tables.descriptions I'd like to relate and use to find/replace in bulk.
[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)
I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".
Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico
[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico
View 4 Replies
View Related
Oct 29, 2012
I need calculated field (in a query) that executes a running cummulative total for three conditions: "="&Project Name, "="&Service and "<="&Date.
I can do it in Excel using SUMIFS(), but have no clue on how its done in Access 2010 !
The "Balance" field should be the end result:
Project Service Date Amount Balance
A Welding 1/1/2012 100 100
A Welding 1/2/2012 120 220
A Wiring 1/3/2012 150 150
B Welding 1/1/2012 103 103
B Painting 1/2/2012 124 124
B Painting 1/3/2012 155 278
C Welding 1/1/2012 106 106
C Wiring 1/2/2012 127 127
C Wiring 1/3/2012 159 286
View 2 Replies
View Related
May 22, 2013
I want my query to find the cumulative forecast up until a best before date.
This is fine if a forecast goes past the best before date as the query picks it out but if the forecast doesn't continue then it won't match them up.
I need the IF statement to say that IF no forecast is present then look at the last cumulative forecast.
How would I go about this?
View 4 Replies
View Related
Mar 25, 2013
I am having a problem with a filter that i am trying to apply to a subform.
I have a button on the form that when clicked should filter the subform (which is in datasheet view) to the criteria i set.
This criteria will eventually run from a combo box but I wanted to just get the filter working first.
I put the following code into the onclick event of the button:
Items.Form.Filter = "Items.Form.[Master Category] = 2"
Items.Form.FilterOn = True
I chose the criteria 2 for the filter just as a test as I knew there are some records with that value in the master category field.
The problem is when ever i click the button to apply the filter it clears all the data as if it has not found any records with that value.
Is my syntax and method OK? Why its filtering everything out?
The only other thing to consider is that the field I am filtering on was set up using a lookup wizard linked to a table so the values stored are a foreign key (hence the value being 2 rather than something descriptive).
View 3 Replies
View Related
Jul 17, 2006
How do I calculate the values between two columns and populate a third column with those new values? I have an ''Actual Cost'' column and ''Budget Cost'' column and I would like to create a third column ''Margin.''
Is this something that needs to be done with a query or can it be handeled in the design view of a table? I'm starting to learn that Access works nothing like Excel.
Thank you in advance for any help. I scanned some of the threads in this forum, but many were beyond my comprehension. Any walk-through of this procedure would be much appreciated.
View 1 Replies
View Related
Sep 29, 2005
I searched the archive for how to store a calculated value and found a lot of controversial dialogue about the pros/cons but not really a solution on how to do it.
I have a form based on a query. The database behind the form and query is our ERP database and is connected though an odbc connection. The form allows the user to enter some shipping dimensions and freight rates. The data is automatically stored in the ERP database and any user can view the data from the ERP software.
Here is the problem. There are also some freight calculations that occur on the form that our business teams want the results stored in the ERP database. They can view the results from the calculations using the ERP software instead having to open a 2nd application (Access form) to view the calculated data.
I have determined which fields in the ERP database will hold the data. I only need the vb code or other suggestions on how to update the calculated values into the database.
I apologize for the long message. Thanks for your help,
Jeff
View 3 Replies
View Related
Feb 3, 2005
Hi all -
I need to create record totals and grand totals on a report where I count up the # of Yes's and No's across 10 fields. I've already created calculated text boxes that come up with the record totals. What I can't figure out for the life of me is how to create a text box calculating the grand total based on the previous calculated fields I created. It seems that access isn't letting me sum a field I created on the report. Do I have to create a query first? Thanks.
View 3 Replies
View Related
