Running Total
in [General]
|
Prev: Multiple listbox questions
Next: Filter using query
From: KenSheridan via AccessMonster.com on 20 Mar 2010 17:07 I think its down to me currently not using my usual machine as a couple of 'smart quotes' have crept in. I draft my replies in Word, and on my normal machine smart quotes are turned off. Unfortunately they show as normal quotes in the Access Monster interface which I use, so if I miss them in Word I don't spot them before sending the post. Sorry for the confusion. Hopefully this should cure it: SELECT AcctYear([Status Date],4,1) AS AYear, DatePart("m",[Status Date]) AS AMonth, Format([Status Date],"mmm") AS FDate, SUM([Total Value]) AS [SumOfTotalValue], (SELECT Format(SUM([Total Value]),"$0,000.00") FROM qryResultsWon AS RW2 WHERE Format(RW2.[Status Date],"yyyymm") <= Format(RW1.[Status Date],"yyyymm") AND [Status] = "Won" AND AcctYear([RW2.Status Date],4,1) = AcctYear([RW1.Status Date],4,1)) AS RunTot FROM qryResultsWon AS RW1 WHERE [Status] = "Won" GROUP BY AcctYear([Status Date],4,1), DatePart("m",[Status Date]), Format([status date],"mmm"); Neil wrote: >I am still getting this error message SYNTAX error in query expression. >SELECT Format(SUM([Total Value]),"$0,000.00") > FROM qryResultsWon AS RW2 > WHERE Format(RW2.[Status Date],"yyyymm”) > <= Format(RW1.[Status Date],"yyyymm”) > AND [Status] = “Won” AND > AcctYear([RW2.Status Date],4,1) = > AcctYear([RW1.Status Date],4,1)) > >Thanks for your help so far. > >Neil > >> Just spotted a paste error in the second SQL statement. I'd put an RW 2 >> where it should have been RW1. It should have been: >[quoted text clipped - 19 lines] >> Ken Sheridan >> Stafford, England -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
From: Neil on 20 Mar 2010 18:29 Ken, Sorry to be a pain, however the statement as is does not run but when I change the two RW1 to RW2 it runs but gives me this output: The run total is correct for the full year but it is not showing the run totals for each month starting with April. AYear AMonth FDate SumOfTotalValue RunTot 2009-10 1 Jan 680255.68 $8,113,323.20 2009-10 2 Feb 324716 $8,113,323.20 2009-10 3 Mar 23623 $8,113,323.20 2009-10 4 Apr 1641724.82 $8,113,323.20 2009-10 5 May 237662.38 $8,113,323.20 2009-10 6 Jun 1395053.72 $8,113,323.20 2009-10 7 Jul 400480.61 $8,113,323.20 2009-10 8 Aug 174660 $8,113,323.20 2009-10 9 Sep 407782.35 $8,113,323.20 2009-10 10 Oct 742920.68 $8,113,323.20 2009-10 11 Nov 312406.77 $8,113,323.20 2009-10 12 Dec 1772037.19 $8,113,323.20 "KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote in message news:a54eb7e429fbc(a)uwe... > I think its down to me currently not using my usual machine as a couple of > 'smart quotes' have crept in. I draft my replies in Word, and on my > normal > machine smart quotes are turned off. Unfortunately they show as normal > quotes in the Access Monster interface which I use, so if I miss them in > Word > I don't spot them before sending the post. > > Sorry for the confusion. Hopefully this should cure it: > > SELECT AcctYear([Status Date],4,1) AS AYear, > DatePart("m",[Status Date]) AS AMonth, > Format([Status Date],"mmm") AS FDate, > SUM([Total Value]) AS [SumOfTotalValue], > (SELECT Format(SUM([Total Value]),"$0,000.00") > FROM qryResultsWon AS RW2 > WHERE Format(RW2.[Status Date],"yyyymm") > <= Format(RW1.[Status Date],"yyyymm") > AND [Status] = "Won" AND > AcctYear([RW2.Status Date],4,1) = > AcctYear([RW1.Status Date],4,1)) > AS RunTot > FROM qryResultsWon AS RW1 > WHERE [Status] = "Won" > GROUP BY AcctYear([Status Date],4,1), > DatePart("m",[Status Date]), > Format([status date],"mmm"); > > Neil wrote: >>I am still getting this error message SYNTAX error in query expression. >>SELECT Format(SUM([Total Value]),"$0,000.00") >> FROM qryResultsWon AS RW2 >> WHERE Format(RW2.[Status Date],"yyyymm”) >> <= Format(RW1.[Status Date],"yyyymm”) >> AND [Status] = “Won” AND >> AcctYear([RW2.Status Date],4,1) = >> AcctYear([RW1.Status Date],4,1)) >> >>Thanks for your help so far. >> >>Neil >> >>> Just spotted a paste error in the second SQL statement. I'd put an RW 2 >>> where it should have been RW1. It should have been: >>[quoted text clipped - 19 lines] >>> Ken Sheridan >>> Stafford, England > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1 >
From: Neil on 21 Mar 2010 05:51 Have worked it out, will post sql tomorrow when I have tidied it up, thanks to everyone for your help. Neil "Neil" <naeves_noreply(a)gmail.com> wrote in message news:eWxQDP4xKHA.3536(a)TK2MSFTNGP06.phx.gbl... > Hi, > > I am trying to use a Running Total query but have come unstuck in two > places, the following works however I would like the running total to > start in April not January and go to March the following year, also how > can I stipulate that it only shows records where the field [Status] is > equal to "Won". > > SELECT DatePart("yyyy",[Status Date]) AS AYear, DatePart("m",[Status > Date]) AS AMonth, Format([status date],"mmm") AS FDate, > Sum(qryResultsWon.[Total Value]) AS [SumOfTotal Value], > Format(DSum("[Total Value]","qryresultswon","DatePart('m',[Status > Date])<=" & [AMonth] & ""),"$0,000.00") AS RunTot > FROM qryResultsWon > GROUP BY DatePart("yyyy",[Status Date]), DatePart("m",[Status Date]), > Format([status date],"mmm") > ORDER BY DatePart("yyyy",[Status Date]), DatePart("m",[Status Date]); > > Hope you can help. > > Neil
From: KenSheridan via AccessMonster.com on 21 Mar 2010 12:44 Neil: I can't pinpoint the precise cause of the problem, but I think it probably stems from a combination of the fact that you are both summing the total value per month and trying to return a cumulative balance for the accounting year, along with the fact that the query is based on another query which itself is summing values. This seems to mess up the correlation of the subquery and outer query. I've tried a number of solutions using subqueries without success, so I think you may have to revert to calling the DSum function. I'd also remove the aggregation of values from the source query, and base the final query on one which firstly restricts the results to the 'won' transactions and secondly computes the accounting year and month number for each date. This simplifies the criteria for the DSum function call considerably. So I've assumed you start with a base\table Transactions like so: Transactions ….TransactionID ….TransactionDate ….TransactionAmount ….Status It may well have other columns, but they are immaterial. Creating a query named qryResults based on this table: SELECT Status, TransactionDate, AcctYear(TransactionDate,4,1) AS AccountYear, Format(TransactionDate,"mmm") AS AcctMonth, Month(TransactionDate) AS AcctMonthNumber, TransactionAmount FROM Transactions WHERE Status="Won"; You can then base the final query on this query like so: SELECT AccountYear, AcctMonthNumber, AcctMonth, SUM(TransactionAmount) AS TotalWon, DSum("TransactionAmount","qryResults", "AcctMonthNumber <= " & AcctMonthNumber & " And AccountYear =""" & AccountYear & """") AS RunTot FROM qryResults GROUP BY AccountYear, AcctMonthNumber, AcctMonth; If using the query as the basis for a report sort the report first by AccountYear and then by AcctMonthNumber to return the rows in the correct order. I've tested the above against a Transactions table with some data over several accounting years and it does return the results as expected, with the cumulative balance computing correctly per month, starting from the beginning of each accounting year. I've not formatted the values as currency, but that's a trivial task, and would in any case best be done in a form or report based on the query rather than in the query itself. Hopefully you'll be able to apply this to your own base table(s) without too much difficulty, but if you have any problems post the details of the structure of the base table or tables involved. Ken Sheridan Stafford, England Neil wrote: >Ken, >Sorry to be a pain, however the statement as is does not run but when I >change the two RW1 to RW2 it runs but gives me this output: The run total is >correct for the full year but it is not showing the run totals for each >month starting with April. > >AYear AMonth FDate SumOfTotalValue RunTot >2009-10 1 Jan 680255.68 $8,113,323.20 >2009-10 2 Feb 324716 $8,113,323.20 >2009-10 3 Mar 23623 $8,113,323.20 >2009-10 4 Apr 1641724.82 $8,113,323.20 >2009-10 5 May 237662.38 $8,113,323.20 >2009-10 6 Jun 1395053.72 $8,113,323.20 >2009-10 7 Jul 400480.61 $8,113,323.20 >2009-10 8 Aug 174660 $8,113,323.20 >2009-10 9 Sep 407782.35 $8,113,323.20 >2009-10 10 Oct 742920.68 $8,113,323.20 >2009-10 11 Nov 312406.77 $8,113,323.20 >2009-10 12 Dec 1772037.19 $8,113,323.20 > >> I think its down to me currently not using my usual machine as a couple of >> 'smart quotes' have crept in. I draft my replies in Word, and on my >[quoted text clipped - 42 lines] >>>> Ken Sheridan >>>> Stafford, England -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1
From: Neil on 21 Mar 2010 16:10
Ken, For me that still summed from January to December so what I did which is probably ugly to you but works for me is: Made another two calculated fields in the 1st query called FYDate (Financial Year Date) and FYDateExt (Financial Year Date Extended) I did the same in the 2nd query but called the fields FyearDate and FinYearDateExt, so for FYDate and Fyeardate I got the year part & month part of the [Status Date] and made them one number, in the FYDateExt and FinYearDateExt I checked for the right two numbers of the FYDate & FyearDate and if they were either 01,02,03 I added a zero to the end of it so that all the numbers were ascending. The 2nd query asks the user for the financial year. It all works with my data giving a running total starting in April of the financial year as shown below in the datasheet view. First query is: SELECT tblResults.[Customer Name], tblResults.[Total Value], tblResults.[GM %], tblResults.Salesperson, tblResults.Status, tblResults.[Status Date], IIf(DatePart("m",[Status date])=1,1,IIf(DatePart("m",[Status date])=2,2,IIf(DatePart("m",[Status date])=3,3,DatePart("m",[Status date])))) AS SMonth, CCur([Total Value]) AS Tvalue, DatePart("yyyy",[Status date]) & DatePart("m",[Status date]) AS FYDate, IIf(Right([fydate],2)="01",[fydate] & "0",IIf(Right([fydate],2)="02",[fydate] & "0",IIf(Right([fydate],2)="03",[fydate] & "0",[fydate]))) AS FYDateExt FROM tblResults GROUP BY tblResults.[Customer Name], tblResults.[Total Value], tblResults.[GM %], tblResults.Salesperson, tblResults.Status, tblResults.[Status Date], CCur([Total Value]), DatePart("yyyy",[Status date]) & DatePart("m",[Status date]) HAVING (((tblResults.Status)="Won")) ORDER BY tblResults.[Status Date]; Second query is: SELECT DatePart("yyyy",[Status Date]) AS SDate, Format([status date],"mmm") AS FDate, CCur(DSum("[Total Value]","qryresultswon1","[FYDateExt]<=" & [FinYearDateExt] & "")) AS RunTot, DatePart("yyyy",[Status date]) & DatePart("m",[Status date]) AS FyearDate, IIf(Right([fyeardate],2)="01",[fyeardate] & "0",IIf(Right([fyeardate],2)="02",[fyeardate] & "0",IIf(Right([fyeardate],2)="03",[fyeardate] & "0",[fyeardate]))) AS FinYearDateExt FROM qryResultsWon1 GROUP BY acctmonth([status date]), DatePart("yyyy",[Status Date]), Format([status date],"mmm"), DatePart("yyyy",[Status date]) & DatePart("m",[Status date]), DatePart("m",[Status Date]), acctyear([Status Date],4,1) HAVING (((acctyear([Status Date],4,1))=[Financial Year])) ORDER BY DatePart("yyyy",[Status Date]); SDate FDate RunTot FyearDate FinYearDateExt 2009 Apr $1,641,724.82 20094 20094 2009 May $1,879,387.20 20095 20095 2009 Jun $3,274,440.92 20096 20096 2009 Jul $3,674,921.53 20097 20097 2009 Aug $3,849,581.53 20098 20098 2009 Sep $4,257,363.88 20099 20099 2009 Oct $5,000,284.56 200910 200910 2009 Nov $5,312,691.33 200911 200911 2009 Dec $7,084,728.52 200912 200912 2010 Jan $7,764,984.20 20101 201010 2010 Feb $8,089,700.20 20102 201020 2010 Mar $8,113,323.20 20103 201030 Regards Neil and once again thank you for your help. "KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote in message news:a558fe8acb107(a)uwe... > Neil: > > I can't pinpoint the precise cause of the problem, but I think it probably > stems from a combination of the fact that you are both summing the total > value per month and trying to return a cumulative balance for the > accounting > year, along with the fact that the query is based on another query which > itself is summing values. This seems to mess up the correlation of the > subquery and outer query. > > I've tried a number of solutions using subqueries without success, so I > think > you may have to revert to calling the DSum function. I'd also remove the > aggregation of values from the source query, and base the final query on > one > which firstly restricts the results to the 'won' transactions and secondly > computes the accounting year and month number for each date. This > simplifies > the criteria for the DSum function call considerably. > > So I've assumed you start with a base\table Transactions like so: > > Transactions > ….TransactionID > ….TransactionDate > ….TransactionAmount > ….Status > > It may well have other columns, but they are immaterial. > > Creating a query named qryResults based on this table: > > SELECT Status, TransactionDate, > AcctYear(TransactionDate,4,1) AS AccountYear, > Format(TransactionDate,"mmm") AS AcctMonth, > Month(TransactionDate) AS AcctMonthNumber, > TransactionAmount > FROM Transactions > WHERE Status="Won"; > > You can then base the final query on this query like so: > > SELECT AccountYear, AcctMonthNumber, AcctMonth, > SUM(TransactionAmount) AS TotalWon, > DSum("TransactionAmount","qryResults", > "AcctMonthNumber <= " & AcctMonthNumber & > " And AccountYear =""" & AccountYear & """") AS RunTot > FROM qryResults > GROUP BY AccountYear, AcctMonthNumber, AcctMonth; > > If using the query as the basis for a report sort the report first by > AccountYear and then by AcctMonthNumber to return the rows in the correct > order. > > I've tested the above against a Transactions table with some data over > several accounting years and it does return the results as expected, with > the > cumulative balance computing correctly per month, starting from the > beginning > of each accounting year. I've not formatted the values as currency, but > that's a trivial task, and would in any case best be done in a form or > report > based on the query rather than in the query itself. > > Hopefully you'll be able to apply this to your own base table(s) without > too > much difficulty, but if you have any problems post the details of the > structure of the base table or tables involved. > > Ken Sheridan > Stafford, England > > Neil wrote: >>Ken, >>Sorry to be a pain, however the statement as is does not run but when I >>change the two RW1 to RW2 it runs but gives me this output: The run total >>is >>correct for the full year but it is not showing the run totals for each >>month starting with April. >> >>AYear AMonth FDate SumOfTotalValue RunTot >>2009-10 1 Jan 680255.68 $8,113,323.20 >>2009-10 2 Feb 324716 $8,113,323.20 >>2009-10 3 Mar 23623 $8,113,323.20 >>2009-10 4 Apr 1641724.82 $8,113,323.20 >>2009-10 5 May 237662.38 $8,113,323.20 >>2009-10 6 Jun 1395053.72 $8,113,323.20 >>2009-10 7 Jul 400480.61 $8,113,323.20 >>2009-10 8 Aug 174660 $8,113,323.20 >>2009-10 9 Sep 407782.35 $8,113,323.20 >>2009-10 10 Oct 742920.68 $8,113,323.20 >>2009-10 11 Nov 312406.77 $8,113,323.20 >>2009-10 12 Dec 1772037.19 $8,113,323.20 >> >>> I think its down to me currently not using my usual machine as a couple >>> of >>> 'smart quotes' have crept in. I draft my replies in Word, and on my >>[quoted text clipped - 42 lines] >>>>> Ken Sheridan >>>>> Stafford, England > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1 > |