Queries :: Running Total Field In Query
May 20, 2015
I have a table with dates in field1 and an amount of seconds in field2.
field1 field2
01/01/2015, 1345
02/01/2015, -132
04/01/2015, 259
I would like to produce a query that performs a running total in the third column like so:
field1 field2 field3
01/01/2015, 1345, 1345
02/01/2015, -132, 1213
04/01/2015, 259, 1472
This is quite simple to achieve in Excel. (eg =SUM($B$1:B3))
What is the query formula for Access?
View Replies
ADVERTISEMENT
Jul 23, 2015
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
View 9 Replies
View Related
Jun 24, 2013
I have qry with these fields: DateOfPayment and Ammount.
I would like to add another field with running total sum. I am trying this:
RunnTot: Format(DSum("[Ammount]";"qryCFSUM"; [DateOfPayment] <=#" & [DateOfPayment] & "#" );"0 000"" Kč""").
But It still does not work.
Example of my data in "qryCFSUM":
DateOfPayment
20.1.2013
31.1.2013
30.3.2013
Amount
1 2000 Kč
15 456 Kč
23 465 Kč
And what I would like to have:
RunnTot
1 200 Kč
16 656 Kč
40 121 Kč
View 14 Replies
View Related
Oct 5, 2013
I want to calculate running total and find out the date when that total is greater than a number.
My initial plan was to use Dsum and then use dlookup to find when that Dsum value > [Fixednum].
But when I try Dsum and use Totals in query, access shuts down. maybe because of 15000 rows.
I have attached a sample database that shows what Im working with and what I would like.
View 2 Replies
View Related
Jun 19, 2013
I have a table ("tbl_idq_all") with a text field for product codes ("scode"), a date field (dd/mm/yyyy) and a quantity field ("po_qty"). This table therefore holds future receipts of stock for products.
What I am having trouble doing is create a running total of [po_qty] based on [scode] and [Date].
A good example is stock code 10254. This has a quantity of 40,032 arriving 01/06/2013 and a quantity of 30,096 arriving 01/09/2013.
Therefore the running totals should read:
scode | Date | po_qty | RunningTotal
10254 | 01/06/2013 | 40032 | 40032
10254 | 01/09/2013 | 30096 | 70128
As you can see from the attached DB I have 70128 repeated twice in the RunningTotal column.
View 8 Replies
View Related
Dec 18, 2013
I am creating a line graph from a running total query to show our income from items shipped for each month. Currently I have the following Code in my query which works but it displays the month as a number in my graph and I would like it to show the Month name.
Code:
SELECT DatePart("yyyy",[ShippedDate]) AS AYear, DatePart("m",[ShippedDate]) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND DatePart('m', [ShippedDate])<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot
FROM tblJobs
WHERE (((tblJobs.ShippedDate) Is Not Null))
GROUP BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate])
ORDER BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate]);
I tried this solution, but I get an error in the RunTot field, I'm assuming because Access can't use the month name in dsum.
Code:
SELECT DatePart("yyyy",[ShippedDate]) AS AYear, MonthName(DatePart("m",[ShippedDate])) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND MonthName(DatePart('m', [ShippedDate]))<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot
FROM tblJobs
WHERE (((tblJobs.ShippedDate) Is Not Null))
GROUP BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate])
ORDER BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate]);
Do any of you know a way I can make this work?
View 2 Replies
View Related
Nov 12, 2014
I have a running total query that seems to run but when I try to total the query results then Access will be "Not Responding". I tried to change it to a Make Table query because I need to use the running total result in another query. So I created a table but when I try to run the make table query it just says "Run Query" at the bottom. Here is the query:
SELECT [OTMissing].[Employee], [OTMissing].[AsOf], [OTMissing].[HRsEarn], (SELECT Sum(OT1.[HRsEarn]) FROM [OTMissing] As OT1
WHERE OT1.[Employee]=[OTMissing].[Employee] AND OT1.[AsOf] <=[OTMissing].[AsOf]) AS RunningTotal, [OTMissing].[RemainPP] INTO OTGenerated
FROM [OTMissing]
ORDER BY [OTMissing].[Employee], [OTMissing].AsOf;
My OTMissing query is 47061 rows. Does that have something to do with it? The only other thing it might be is that most of the records have 0 although I'm not sure why it would be a problem I thought I would at least mention it.
View 2 Replies
View Related
Aug 5, 2014
I am trying to create a running total query that aggregates project funding by fiscal year. The fiscal year is calculated based on a date time field that is never null. The totals field comes from 2 different number fields that are either 0 or > 0. The query is going to be linked to by Excel, so I have to do the running total in the query itself, vs. a report.It is close to working, except that it is not totalling the first fiscal year. The output surrently looks like this:
FYear BudgetedCostIndCont Commitment
2010
2011 8585643 4742000 3843643
2012 2297116511432165 11539000
2013 3618726216963282 19223980
2014 4457769020706644 23871046
2015 4963815023206644 26431506
As you can see, the first row for FY 2010 is blank. I know there is data there, as this query is fed by a subquery that selects these rows based on contract signed date. Below is the SQL of each query:
Code:
SELECT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1
AS FYearExport
FROM tblProject
GROUP BY Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.ProjID, tblProject.FPAccepted
HAVING (((tblProject.FPAccepted)=True));
and the Aggregate query:
Code:
SELECT qryDashboardChart1.FYearExport,
DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "")
AS RunTotBudgetedCost, ([RunTotBudgetedCost]-[RunTotTECTERRACommitment])
[code]....
I should also mention that I cannot implement the NZ() function, as Excel balks at this when trying to link to Access queries.
View 8 Replies
View Related
Oct 12, 2013
I have a list of products that have a loan payment associated to them. To cover these loans, we have incoming revenue for each product at different dates.
The incoming revenue is a field of running sum of revenue for each product.
Desired output:
I want to how how much % of loan (jn total; for all products) is paid in October, november and december and as such (as cumulative). i.e. total of 40% in Oct, 70% in Nov and 100% by Dec etc.
I am attaching the database, with sample data.
View 1 Replies
View Related
Feb 17, 2006
I have a query, and I want a field that shows the current balance as of that transaction, eg:__________________________________________________ _______|__Transaction ID__|__Type_______|__Amount__|__Balance__||__1______________|__Deposit_____|__£10______|__£1 0_______||__2______________|__Deposit_____|__£30______|__£4 0_______||__3______________|__Withdrawal__|__£15______|__£2 5_______|So far, I have this:SELECT [Transaction ID], [Type], [Amount], (SELECT Sum([Amount]) FROM tblTransactions AS tblTransactions2 WHERE tblTransactions2.[Transaction ID] <= tblTransactions.[Transaction ID]) AS BalanceFROM tblTransactionsWHERE [Account ID]=1;Although this does not look at the 'Type' field; it just adds the amounts; regardless of it being a deposit or withdrawal. I'm really not sure how to add this.Thanks in advance.
View 11 Replies
View Related
Jul 15, 2015
=([frmservices_2013].[Form]![sub2013])
I have this expression in a field on a form. It's been a really long time since I worked in Access. Is sub2013 a field on a form, in a table?
It's doing a running total of a field from a form. I can't find a subform with this name, or a field anywhere, I'm drawing a blank.
using Access 2007....
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
Apr 2, 2015
Is it possible to have a running total either in a query or using the Running Sum function on a text box on a report that will reset after a specific value. Here is what I would like to have happen:
The RunningTotalCube field to reset when it has reach 2.3 or whatever number comes closest to that number.
Date Time Item Cube RunningTotalCube
4-2-15 12:05 15615 0.5 0.5
4-2-15 12:06 15918 0.8 1.3
4-2-15 12:10 98563 0.5 1.8
4-2-15 12:12 45268 0.4 2.2
4-2-15 12:15 25854 0.9 0.9 {reset}
4-2-15 12:17 75136 0.5 1.4
Is this possible either in the query or the report/in Access or in VBA?
View 3 Replies
View Related
Oct 23, 2013
I have a quick (hopefully) query about adding up consecutive values to get a running total.
Currently I have the following
ID Value
1 0
2 5
3 2
4 0
5 1
6 30
7 2
etc...
I am looking for a way to get a running total up to the point of a 0 then restarting. e.g.
ID Value Running total
1 0 0
2 5 5
3 2 7
4 0 0
5 1 1
6 30 31
7 2 33
View 12 Replies
View Related
Mar 21, 2014
I am having an issue with my running total query.
It consists of a running total per vehiclenum. All data comes from one table.
It works properly only on the first vehiclenum of the query. After that, the first "previous" odometer reading of each subsequent vehiclenum starts at some erroneous number, throwing the remainder of each vehiclenum running total.
Here is the code for the query,
SELECT qry_ODO_TotalSub.ID AS OdomAlias, qry_ODO_TotalSub.ODate, qry_ODO_TotalSub.VehicleNum, qry_ODO_TotalSub.Odometer, Nz(DLast("Odometer","qry_ODO_TotalSub","[ID] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference, Nz(DFirst("Odometer","qry_ODO_TotalSub"),0) AS StartOD, [Odometer]-[StartOD] AS RunningSum
FROM qry_ODO_TotalSub
ORDER BY qry_ODO_TotalSub.ID;
View 4 Replies
View Related
Jul 1, 2013
I am having trouble with this running total. Let's say I have a well. I am trying to create a running total that calculates the total Uptime (or hours operational) for each well every month.
I attached a PDF with an example of what I am working with.
View 10 Replies
View Related
Mar 12, 2007
Does anyon ehave any experience of running totals in an access query.
I'm reporting the data through excel not access reports so need a query not a report solution..
I have a table which looks:
RegionCategoryTypeDesc Period_IDPeriod_YTDPeriodTotal
CanadaEventsWSOP Team67Budget15000
CanadaEventsWSOP Team78Budget0
CanadaEventsWSOP Team89Budget0
CanadaEventsWSOP Team910Budget0
CanadaEventsWSOP Team1011Budget0
CanadaEventsWSOP Team1112Budget0
CanadaEventsWSOP Team1213Budget0
CanadaEventsTOTALAll12Budget15000
CanadaEventsTOTALAll23Budget15000
CanadaEventsTOTALAll34Budget15000
CanadaEventsTOTALAll45Budget15000
CanadaEventsTOTALAll56Budget15000
What I would like is to have an additional column which keeps a monthly summary of spend based on running total month 1to 12. All items have months 1 - 12 and are ordered in that fashion.
Any helpo really appreciated.
Simon
View 1 Replies
View Related
Aug 22, 2007
Hi,
Am trying to create a query for a chart where I can total the employees over time but am having real trouble creating a running total from the "Total" field within a query but cannot seem to get it at all.
TotalStartDateLeftDate RunningTotal
126/03/1957
121/03/1971
127/02/1986
115/02/1988
207/03/1988
007/03/198831/05/2007
Here is my current SQL query:
SELECT Sum([CountOfStartDate]-[CountOfLeftDate]) AS Total, Atest1.StartDate, Atest1.LeftDate, Sum([CountOfStartDate]-[CountOfLeftDate]) AS RunningTotal
FROM Atest1
GROUP BY Atest1.StartDate, Atest1.LeftDate;
Can anyone help please?
View 5 Replies
View Related
Mar 10, 2008
This should be easy! Right?
I have a series of dates with events that occured on those dates. Some events were extended, others were not how do I get a running total, cumulative total, for all records in the RunTotal column?
Opened DateOpen IssuesCountOfExtendedNotExtRunTotal
5/21/2007 1 10 1
8/6/2007 1 10 2
10/8/2007 1 10 3
11/1/2007 1 10 4
11/8/2007 1 01 5
12/5/2007 1 0 1 6
Thanks for your help.
View 6 Replies
View Related
Jun 11, 2015
I have an access form (Customer) along with a sub-form (Work_done). On sub-form I have Running sum of Amount in Text box-1. I want that Total of Running sum be replaced with Main form's tables field total_amount. Is it possible that we can replace an amount of control of sub-form into main form's field?
View 1 Replies
View Related
Jun 3, 2015
I have a table of prices for commodity contracts, with my first field showing the dates the prices are from and the subsequent fields relating to the individual contracts (contract A, contract B, contract C, etc.)
I want to run a query that allows a date range to be selected, and a contract to be selected.
the first part I am pretty sure i know how to do (Between [Enter Period Start:] And [Enter Period End:]), but its selecting which contract i want this range to apply to that I am not sure how to do.
Can this be done in the same query? or would i have to do something like create a separate query for each contract and then use a form with a selection box that chooses which query to run?
View 7 Replies
View Related
Jul 31, 2013
the relevant fields look something like this:
TellerNum - Date - Field1 - Field2 - Field2
I need a way for my query to take all rows with the same TellerNum and add Field1, Field2, and Field3 together for those records. So, there may be 20 records with the same TellerNum, and I need Field 1, 2, and 3 to be added together and then totaled for all 20 records to give me 1 grand total. I need this to be done for each TellerNum, so that the results will be a grand total for each TellerNum. So, the results may look something like this:
TellerNum 1: 486
TellerNum 2: 300
TellerNum 3: 240
etc..
View 3 Replies
View Related
Apr 13, 2006
I am a newbie, so please forgive me for such an easy question, but I am stumped. I attached a text file that shows the data I am working with. What I need to do is take each product (labeled Prod) and do a weekly sum on the quantities and compare against a set number to see if the quantity is lower or higher. For instance, I need to take column 12, regardless of value and compare it against set number. If the quantity is less, then I need to add the value of column 12 to column 13 and compare the summed value against set number. Again, if the sum is less than set number, I then need to take the value of column 14 and add it to the summed value of the previous step (sum of 12 & 13), then compare this new sum to set number. This process keeps taking place until I reach a summed value that is greater than set number. Once that happens I need to identify the column that sent me over the set value and hold that data. For instance, if column 33's (out of 52) summed value takes me over the set number, I want to know that it was column 33, so I can run further calculations against that value. The column header's are week numbers and I need to identify order points based on lead times and when I will run out of material. Is this beyond queries? I think so, but if it is, I don't know how to exactly begin the code in VBA either. I think I would use an If then Else stucture with a counter switch set from 1 to 52, unless comparison exits function, but not certain. HELP?????:confused:
View 3 Replies
View Related
Mar 14, 2005
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
Many thanks in advance.
Peter
View 2 Replies
View Related
Dec 4, 2007
Ahhhh this is doing my nugget in!!! I have a simple table with 4 fields
ID (unique number)
DATE (date)
CAPACITY (number of SKU we can hold)
ORDERS (number of SKU on order)
the data looks like this
ID DATE CAPACITY ORDERS
1 01/01/2007 250000 250000
2 02/01/2007 250000 300000
3 03/01/2007 250000 300000
4 04/01/2007 250000 300000
So looking at the above table we can see that we have more orders than capacity in our factory, however they require to see this in graph form, so what I need is for each ID a running total of the CAPACITY and ORDERS so over a given date range i would produce a graph to find the "pinch points" where we could see if the capacity is less than the orders we have over time.
so my new table would be:
ID DATE CAPACITY ORDERS CAPRUN ORDRUN
1 01/01/2007 250000 250000 250000 250000
2 02/01/2007 250000 300000 500000 550000
3 03/01/2007 250000 300000 750000 850000
4 04/01/2007 250000 300000 1000000 1150000
etc. which i would create my graph from. Ive looked at Dsum and some other methods but cant get my head around it so any help will be much appreciated.
Thanks Steve.
View 14 Replies
View Related
Dec 27, 2004
Hi
I'm trying to create a database to keep track of invoices .
on work that was done.is there any sample database that I could take a look at.Or can anyone help me on this I'm trying to capture price on parts + price on labor = total the order form in the tradewinds database looks good but don't know where the code is for calulations? can anyone help me out?
Thanks
Tom
View 9 Replies
View Related
