Running Total
in [General]
|
Prev: Multiple listbox questions
Next: Filter using query
From: KenSheridan via AccessMonster.com on 19 Mar 2010 20:14 If the query is covering more than one accounting year you'll need to include that in the criteria for the DSum function, along with the restriction on the Status column. Try this: 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], Format(DSum("[Total Value]","qryresultswon", “Format([Status Date],”"yyyymm"”) <= ""“ & Format([Status Date],"yyyymm”) & “"" AND [Status] = “”Won”” AND AcctYear([Status Date],4,1) = “”“ & AcctYear([Status Date],4,1) & """") ,"$0,000.00") AS RunTot FROM qryResultsWon WHERE [Status] = “Won” GROUP BY AcctYear([Status Date],4,1), DatePart("m",[Status Date]), Format([status date],"mmm"); Or using a subquery: 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(RW2.[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"); As you see using a subquery is rather simpler because you don't have to mess about with all the concatenation and making sure that text values are delimited with literal quotes as you do when building the criteria for the DSum function. Ken Sheridan Stafford, England Neil wrote: >Hi Ken, > >Thanks for that however I am having trouble when trying to update the dsum >function. When I put it in it just reverts back to what I already have. >Could you please explain how I go about it. > the acctyear function works OK it is just the rest of the query that I >can't get to work, also tried your last post > >Neil. > >> The following function will return the accounting year for any date in the >> format 2009-10 (as for today with an accounting year starting 1 April). >[quoted text clipped - 62 lines] >>> >>>Neil -- Message posted via http://www.accessmonster.com
From: KARL DEWEY on 19 Mar 2010 22:51 Tested -- SELECT Format([Status Date],"mmmm yyyy") AS AMonth, qryresultswon.[Total Value], format((SELECT sum([XX].[Total Value]) from qryresultswon as [XX] WHERE [XX].[Status Date] between qryresultswon.[Status Date] and DateAdd("m",-11,DateSerial(Year(Date()),Month(Date())-1,1))), "$0,000.00") AS RunTot FROM qryresultswon WHERE (((qryresultswon.Status)="Won") AND ((qryresultswon.[Status Date]) Between DateAdd("m",-11,DateSerial(Year(Date()),Month(Date())-1,1)) And DateSerial(Year(Date()),Month(Date()),0))) GROUP BY Format([Status Date],"mmmm yyyy"), qryresultswon.[Total Value], qryresultswon.[Total Value], qryresultswon.[Status Date], Format([Status Date],"yyyymm") ORDER BY Format([Status Date],"yyyymm"); -- Build a little, test a little. "Neil" wrote: > Sorry I should have stated that the error message that I get is syntax error > in the following section: > > SELECT Sum([XX].[Total Value]) FROM > qryresultswon AS [XX] WHERE ([Status Date] Between DateSerial(Year(Date()), > Month(Date())-1, 1) AND DateSerial(Year(Date()), Month(Date()), 0)) AND > ([XX].Status = "Won"),"$0,000.00") > > Neil > > "Neil" <naeves_noreply(a)gmail.com> wrote in message > news:ufx8Ya6xKHA.2644(a)TK2MSFTNGP04.phx.gbl... > > Karl, > > > > I get this section highlighted when I try and run this. > > > > SELECT Sum([XX].[Total Value]) FROM > > qryresultswon AS [XX] WHERE ([Status Date] Between > > DateSerial(Year(Date()), > > Month(Date())-1, 1) AND DateSerial(Year(Date()), Month(Date()), 0)) AND > > ([XX].Status = "Won"),"$0,000.00") > > > > Neil > > > > "KARL DEWEY" <KARLDEWEY(a)discussions.microsoft.com> wrote in message > > news:683CB815-90C1-4C56-88A5-5C970B11BE9A(a)microsoft.com... > >> Try this -- > >> 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(SELECT Sum([XX].[Total Value]) FROM > >> qryresultswon AS [XX] WHERE ([Status Date] Between > >> DateSerial(Year(Date()), > >> Month(Date())-1, 1) AND DateSerial(Year(Date()), Month(Date()), 0)) AND > >> ([XX].Status = "Won"),"$0,000.00") AS RunTot > >> FROM qryResultsWon > >> WHERE qryResultsWon.Status = "Won" > >> GROUP BY DatePart("yyyy",[Status Date]), DatePart("m",[Status Date]), > >> Format([status date],"mmm") > >> ORDER BY DatePart("yyyy",[Status Date]), DatePart("m",[Status Date]); > >> > >> -- > >> Build a little, test a little. > >> > >> > >> "Neil" wrote: > >> > >>> 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: Neil on 20 Mar 2010 04:49 Hi Karl, Please forgive my lack of knowledge in this matter, your sql gives the following results with my data however what I am trying to do is shown below which comes from another query however what I am after is that the RunTot starts in Apr 2009 and finishes in Mar 2010 whereas this one starts in Jan 2010 and goes to Dec 2009 by month. Any further help would be greatly appreciated. Neil AMonth Total Value RunTot April 2009 400 $1,524,286.82 April 2009 432 $1,593,925.82 April 2009 520 $1,530,835.82 April 2009 792 $1,593,493.82 April 2009 875 $1,573,647.82 April 2009 875 $1,617,325.82 April 2009 1031 $1,514,842.82 April 2009 1430 $1,572,772.82 April 2009 1454 $92,233.00 April 2009 1594 $1,571,342.82 April 2009 1632 $97,705.00 April 2009 1699 $1,616,450.82 April 2009 1872 $1,569,748.82 April 2009 1900 $1,641,724.82 April 2009 1964 $1,639,824.82 April 2009 1982 $112,091.00 April 2009 2143 $1,619,468.82 April 2009 2188 $1,600,530.82 April 2009 2188 $1,602,718.82 April 2009 2188 $1,604,906.82 and then goes down to Mar 2010 AYear AMonth FDate SumOfTotal Value RunTot 2009 4 Apr 1641724.82 2670319.5 2009 5 May 237662.38 2907981.88 2009 6 Jun 1395053.72 4303035.6 2009 7 Jul 400480.61 4703516.21 2009 8 Aug 174660 4878176.21 2009 9 Sep 407782.35 5285958.56 2009 10 Oct 742920.68 6028879.24 2009 11 Nov 312406.77 6341286.01 2009 12 Dec 1772037.19 8113323.2 2010 1 Jan 680255.68 680255.68 2010 2 Feb 324716 1004971.68 2010 3 Mar 23623 1028594.68 "KARL DEWEY" <KARLDEWEY(a)discussions.microsoft.com> wrote in message news:80C3BB44-CB3E-4FBE-97DF-68FB4BC5EF0E(a)microsoft.com... > Tested -- > SELECT Format([Status Date],"mmmm yyyy") AS AMonth, qryresultswon.[Total > Value], format((SELECT sum([XX].[Total Value]) from qryresultswon as [XX] > WHERE [XX].[Status Date] between qryresultswon.[Status Date] and > DateAdd("m",-11,DateSerial(Year(Date()),Month(Date())-1,1))), "$0,000.00") > AS > RunTot > FROM qryresultswon > WHERE (((qryresultswon.Status)="Won") AND ((qryresultswon.[Status Date]) > Between DateAdd("m",-11,DateSerial(Year(Date()),Month(Date())-1,1)) And > DateSerial(Year(Date()),Month(Date()),0))) > GROUP BY Format([Status Date],"mmmm yyyy"), qryresultswon.[Total Value], > qryresultswon.[Total Value], qryresultswon.[Status Date], Format([Status > Date],"yyyymm") > ORDER BY Format([Status Date],"yyyymm"); > > -- > Build a little, test a little. > > > "Neil" wrote: > >> Sorry I should have stated that the error message that I get is syntax >> error >> in the following section: >> >> SELECT Sum([XX].[Total Value]) FROM >> qryresultswon AS [XX] WHERE ([Status Date] Between >> DateSerial(Year(Date()), >> Month(Date())-1, 1) AND DateSerial(Year(Date()), Month(Date()), 0)) AND >> ([XX].Status = "Won"),"$0,000.00") >> >> Neil >> >> "Neil" <naeves_noreply(a)gmail.com> wrote in message >> news:ufx8Ya6xKHA.2644(a)TK2MSFTNGP04.phx.gbl... >> > Karl, >> > >> > I get this section highlighted when I try and run this. >> > >> > SELECT Sum([XX].[Total Value]) FROM >> > qryresultswon AS [XX] WHERE ([Status Date] Between >> > DateSerial(Year(Date()), >> > Month(Date())-1, 1) AND DateSerial(Year(Date()), Month(Date()), 0)) AND >> > ([XX].Status = "Won"),"$0,000.00") >> > >> > Neil >> > >> > "KARL DEWEY" <KARLDEWEY(a)discussions.microsoft.com> wrote in message >> > news:683CB815-90C1-4C56-88A5-5C970B11BE9A(a)microsoft.com... >> >> Try this -- >> >> 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(SELECT Sum([XX].[Total Value]) >> >> FROM >> >> qryresultswon AS [XX] WHERE ([Status Date] Between >> >> DateSerial(Year(Date()), >> >> Month(Date())-1, 1) AND DateSerial(Year(Date()), Month(Date()), 0)) >> >> AND >> >> ([XX].Status = "Won"),"$0,000.00") AS RunTot >> >> FROM qryResultsWon >> >> WHERE qryResultsWon.Status = "Won" >> >> GROUP BY DatePart("yyyy",[Status Date]), DatePart("m",[Status Date]), >> >> Format([status date],"mmm") >> >> ORDER BY DatePart("yyyy",[Status Date]), DatePart("m",[Status Date]); >> >> >> >> -- >> >> Build a little, test a little. >> >> >> >> >> >> "Neil" wrote: >> >> >> >>> 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 20 Mar 2010 07:55 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: 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"); 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 16:26
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 "KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote in message news:a549e64131482(a)uwe... > 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: > > 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"); > > Ken Sheridan > Stafford, England > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1 > |