Cumulative Monthly Totals?
May 31, 2006
Hi,
I'm trying to create an expression that will calculate cumulative monthly totals but my expression seems to only calculate totals for all months
e.g
MonthDirect DespatchesCum Direct Despatches
2006/031580 21867
2006/0410681 21867
2006/059606 21867
The expression I’m using is
Cum Direct Despatches: (Select Sum([Direct Despatches]) from QRY_DirectDespatches_ByMonth_ByModel)
The query should display the following results -
MonthDirect DespatchesCum Direct Despatches
2006/031580 1580
2006/0410681 12261
2006/059606 21867
I'm using Access 2002 on XP.
Can anyone please advise me where i'm going wrong?!
Thanks in advance for the response
View Replies
ADVERTISEMENT
Nov 9, 2005
I have two tables, one for budget and the other for actual expenditure. I need a query or queries that will give me a cumulative figure on 3 groups based on the expenditure table. In my expenditure table I have the following fields.
ExpenditureID Autonumber
Period Text
CostCentre Text
CostCode Text
Value Currency
fldDate Short Date
I have managed to fnd a function that will give me a running sum for each individual group, but cannot seem to get it to work for more than 1 group. The function is this.
Function fncRunSum(lngCatID As Long, lngUnits As Long) As Long
'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long
If lngID <> lngCatID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = lngCatID
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngUnits
End If
'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function
Is there any way I can see the results in one query or table? Any help appreciated.
View 6 Replies
View Related
May 26, 2006
Hi,
I'm having trouble trying to create an expression for generating cumulative totals by Month.
I believe this is the function I need to use -
DSum (expression, domain, [criteria] )
I need the layout to be as follows -
Months - Rows
Cumulative Monthly Despatches - Column
I have a field list with
Months
Despatches
Models
All of these are from another Query and 'Despatches' is an expression generated in the other query
Can anyone please Help?!
View 1 Replies
View Related
Jul 31, 2006
Hi all,
New user here so apologies if I post in the wrong place.
My colleague and I are trying to put together a database which automates a very time consuming process which is currently being done in Excel.
The purpose of the database is to pull together a load of actuals from SAP and then the forecasts we have put together and then chuck out a whole load of graphs and a summary spreadsheet/ report for the upper echelons;ons of management.
The data is in the database and so far it is all going well - but we have hit a stumbling block. We need Access to calculate some cumulative totals so that we can throw the whole lot into our excel graphs but we can't figure out how to do it.
We are using ye olde Access 97 so our options are a little limited.
Can anyone give us a starter for 10? If you need anything more technical, I can post whatever information you need.
Thanks
Tasha
View 8 Replies
View Related
Mar 13, 2013
I have an excel report which I would like to run through Access to drive trend analysis and compare with other similar reports. The excel report has a cumulative spend figure each week and not the actual weekly spend numbers, the budget figure also can change depending on the actuals.
Excel report:
Week 1
Product ID
Customer
Yearly Budget
Spend
1122
Sam
100
3
1123
John
200
4
[code]...
Will I need to create a new table each week or can I link the file and it updates automatically?Can Access store the weekly data and just update it one week at a time?
View 3 Replies
View Related
Jul 29, 2013
i have a table with health facilities (A,B,C,D) . each health facility has data from several months (Jan, Feb, Mar etc). the table has 2 fields (New Patients) and (Cumulative Patients) . Cumulative Patients is a total of New Patients for current month plus the total patients for the previous month. In the Facility Footer of the report if i create text boxes with data =Sum([New Patients]) and =([Cumulative Patients]) works well.
However in the Report Footer =Sum([New Patients]) works but =Sum([Cumulative Patients]) totals everything. if i try =([Cumulative Patients]).
View 8 Replies
View Related
Jun 1, 2013
I've got a simple invoice database with 10 amount fields, that needs to be summed up intoa Total including Gst box on a monthly report.
Report only has Date, Invoice Number And Total Amount fields on it, so I need to pull the information from table and sum it into Total Amount.
ie. = sum ( t1 +t2 + t3 + t4 + t5 + t6 + t7 + t8 + t9 + t10)
and sum above divided by 10% then added together to form total.
I'm just not sure how to do it, everything I've tried so far ain't worked.
Every example I've looked at is only doing maths with 1 field.
View 4 Replies
View Related
Mar 21, 2013
I am VERY, and I mean VERY new to Access. I've been racking my brain all afternoon and googling like crazy. I just completed two levels of training on Access 2010 and have never worked with the program before. I already set up my tables and now I am on to querying. I have a table with several columns, two of which are "Start Time" and "End Time". I already created a query using the DateDiff function to calculate the time difference for each record. It output a new field with the time difference in hours. Now, I want to sum the totals of the time differences by month and I cannot for the life of me figure it out. My new query has Date (m/dd/yyy) and Hours.
View 14 Replies
View Related
Apr 22, 2015
I have a query for loan calculation, fields are;
Loanamount
loandate
monthlyinstalment
what i want is that query to start subtracting lmonthlyinstalment from loanamount on monthly basis
View 3 Replies
View Related
Sep 4, 2007
Hey all! This is my first post. Been searching through the net all day trying to find a solution to this problem. Basically i have a table that looks like this (regular text is what i have and bolded text is what I need:Name Date Qty MOBrad 12/12/2007 23323 4423John 12/11/2007 3445 4432 John 12/11/2007 344 4432 John 12/11/2007 45 4432 John 12/11/2007 44 4432 John 12/11/2007 3445 4432 Grand Total: (Qty)And then I'd like to be able to carry this over and display a grand total at the bottom of every page of a report that I would need to generate. Our company produces forms and we sometimes have 60 - 70 people working on a single job. We want to see their hours individually but we would also like to see a grand sum of all their hours. If someone could help with this or needs more info let me know. Thanks for all your help!
View 14 Replies
View Related
May 23, 2005
How to make calculated field that represents cumulative row?
For example – table has fields "transaction_number", "trans_date" and "trans_amount".
In this table I register money transactions on my bank-account. I need calculated field that is going to show how much money is on my account after every transaction.
Thanks
View 8 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
Sep 24, 2007
Good afternoon,
I am trying to figure out how to create a cumulative sum field for a test report I am working on. On a very simple level, I can run queries to get my data into the following format:
Test # | Article # | Test Time | Test Parameters
1 | 1 | 8 | A,B,C
2 | 2 | 5 | A,B,C
3 | 2 | 7 | A,B,C
4 | 2 | 9 | A,B,C
For each test number, I want a separate report page showing the article number, test time, and test parameters. Also on each page, I want to display the cumulative test time for all test numbers up to the displayed test number, but only for the article used in that test.
For this example, page three would have test number 3, article number 2, test time 7, test parameters A,B,C, and cumulative test time on article 2 of 12 (5+7), while page four would have a cumulative test time on article 2 of 21 (5+7+9).
How can I convince Access to do this for me? So far I've only been able to have a cumulative test time for all test articles, not just the test article related to the test number. To make this more complicated, Access needs to be able to have another article added at any time without having to rework the code to create the test reports.
I appreciate any help you can give me that will get me on my way.
Thanks!
Erik
View 4 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
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
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
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
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
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
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
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
Jan 28, 2013
I have a production application in which i have a table named daily_production with fields as ( prod_id, date, productname, qty ). Now I want a daily production query as
date : xx-xx-xxxx
productname | qty | monthlysum(for thsi product) | daily average |
I want this on a single query so that i can make a report out of this .
View 12 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
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 3 Replies
View Related
Dec 29, 2014
I hope this amendment to the code for the file attachment that doing a cumulative collection of the items purchased, but only "product code and place of storage," where the code works efficiently with the condition class code but I can not be modified to work two conditions together.
View 2 Replies
View Related
