Queries :: Showing Running Total For Each Month?

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 Replies


ADVERTISEMENT

Queries :: Formatting Month Name In Running Total Query

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

Queries :: Self-Referencing Running Total Used To Calculate Next Total In A Query

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

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 1 Replies View Related

Queries :: Adding Another Field To SUM Running Total

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

Queries :: Running Total In Calculated Field

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

Queries :: Total By Each Month Of The Year

Feb 6, 2014

I have a query that I need to total by each month of the year. Field names and types are as follows

ID (PK)
Month (Date/Time)
PoliceResponse (1 or 0)
TerminalCheckTrue (1 or 0)
RampCheckTrue (1 or 0)
AOACheckTrue (1 or 0)
TotalTerminalRampAOA (TerminalCheckTrue + RampCheckTrue + AOACheckTrue)
TotalChecks (PoliceResponse+TerminalCheckTrue + RampCheckTrue + AOACheckTrue)

Now, I need to just get the total of each of these fields per month for the entire year. So it would look like

Jan, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Feb, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...
Mar, MonthlyPoliceResponse, MonthlyTerminalChecks, etc...

Below is a sample from the query. I guess Im just not sure where to start since there are multiple months the same value, but i need them to total up..

Code:
SELECT DISTINCTROW tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy') AS [Month], Sum(IIf([CKPoliceResponse],1,0)) AS PoliceResponseTrue, Sum(IIf([CKTerminalCheck],1,0)) AS TerminalCheckTrue, Sum(IIf([CKRampCheck],1,0)) AS RampCheckTrue, Sum(IIf([CKAOACheck],1,0)) AS AOACheckTrue, ([TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalTerminalRampAOA, ([PoliceResponseTrue]+[TerminalCheckTrue]+[RampCheckTrue]+[AOACheckTrue]) AS TotalChecks
FROM tblBlotter
GROUP BY tblBlotter.ID, Format$([tblBlotter].[EntryDate],'mmmm yyyy'), Year([tblBlotter].[EntryDate])*12+DatePart('m',[tblBlotter].[EntryDate])-1, Year([tblBlotter].[EntryTime])*12+DatePart('m',[tblBlotter].[EntryTime])-1;

View 14 Replies View Related

Queries :: Running Total With Limit - Reset After Specific Value

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

Queries :: Adding Consecutive Values To Get A Running Total

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

Queries :: Running Total Multiple Record Values

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

Queries :: Running Total To Calculate Uptime Monthly

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

Queries :: Running Total With Text And Date Field?

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

Queries :: Make Table Query Not Working Because Of Running Total?

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

Queries :: Running Total Query Not Calculating First Fiscal Year

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

Queries :: Database For College - Calculate Total Of Lessons Taken In A Month

Jan 12, 2015

I'm currently making a database for college about a driving school.

I have a table that stores the following: booking id, student id, lesson id, student forename, student surname, cost per hour and date paid.

How to create a query that calculates the total of the lessons taken in a month.

View 4 Replies View Related

Running Balance As Opposed To Running Total

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

Running Total

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

Running Total

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

Running Total

Mar 28, 2006

Im having a problem doing a running total on my form.
I want the result to be displayed in a text box, with the figures being collected from a column of figures.

Any help is appreciated

View 2 Replies View Related

How To Maintain A Running Total

Sep 11, 2005

My friends, please help me figure this out. I am new to MS-ACCESS. I am trying to create a simple Leave system for my office. When a user requests a leave, the number of hours will be added to a table. I have created a form for this purpose. What I would then like to happen is, the next field in the Table is the sum of hours requested thus far. So that field would be Requested + Total requested Thus far. I can do it easily in Excel but I can't figure out how to do that in ACCESS. It is a very simple database and I can mail you my sample if you are interested. Thanks in advance.

yallah.

aliyallah@yahoo.com

View 2 Replies View Related

Running Total In Query

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

Percentage And Running Total

Feb 26, 2007

Hi guys


I am making a query that calculates how much costs i have per job. I'd like to create a function in the query that can calculate how big a percentage each job is. (need total for every job/ total for all jobs) but so far i haven't been succesful in this.


Anyone who could help me?


Cheers,


Takstein

View 3 Replies View Related

Trouble Getting A Running Total

Mar 2, 2007

I have a table called Team Standing that includes [TEAM] [PLAYER] [GAME DATE] [POINTS]. From this table I have a query that provides for each game date and team a count of the players, [GAME DATE] [TEAM] [PLAYER COUNT]. [PLAYER COUNT] is calculated using the total COUNT .

What I would like to add is a cumulative running total over the game dates. For example.

[GAME DATE] [TEAM] [PLAYER COUNT] [TOTAL PLAYER COUNT]

2007/01/05 --- 1 ------- 11 -------------- 11
2007/01/12 --- 1 ------- 8 -------------- 19
2007/01/19 --- 1 ------- 14 --------------- 33

I am having trouble figuring out how to get a cumulative running total. I have tried a number of different sub-queries and selects but I can’t seem to get something that works.

Any help or ideas would be greatly appreciated.

Thanks

View 3 Replies View Related

Running Total Query

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

Running Total Per Employee

Sep 16, 2007

I've been tasked to create a report that shows the date an employee hit a loss of $200.00 or greater for the company. Each day they work, they will either have an overage or a shortage in their till fund. They start out each day with a set amount of money in their till and at the end of the day they are expected to turn in that same amount; the tills are used for making change. If they are short, and the amount is fairly large, hence the $200.00 mark, then it becomes an issue that needs investigating.

I need to total these amounts up and when the total hits a negative $200.00 or greater, I need to display the date it occurred and then display the amount the company would have saved if the employee had been either terminated or transferred to a non-revenue department.

Getting the "saved" amount should not be a problem once I figure out how to get the above indicated loss amount.

Any ideas?

Kevin

View 3 Replies View Related

Running Total For Grants

Oct 10, 2007

Hello, I am new so if I am posting in the wrong board, please feel free to move this.

My question is as follows:

I have several tables set up to track the grants my office adminsters. These include Project Information, and Payments. I am attempting to keep a running total (in each funding source) that shows up in the Payments form. I am not sure how to build the query for this.

Example:
Grant A has the following funding:
$20,000 -Capital Projects FY04
$50,000-Federal Funds FY06

The following payments have been made (each payment would be a seperate record:
$1,000- Cap FY04
$2,000- Fed FY06
$4,000-Cap FY04
$8,000-Fed FY06

Suggestions?
PS-I am really not that great with VB so if you could explain it as if I were 5 that would be great

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved