Running Total
in [General]
|
Prev: Multiple listbox questions
Next: Filter using query
From: Neil on 19 Mar 2010 12:46 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 19 Mar 2010 14:10 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). Public Function AcctYear(DateVal As Date, MonthStart As Integer, DayStart As Integer) As String Dim dtmYearStart As Date ' get start of accounting year for date value dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart) ' if date value is before start of accounting year ' accounting year is previous year - this year, ' otherwise its this year - next year If DateVal < dtmYearStart Then AcctYear = Year(DateVal) - 1 & Format(Year(DateVal) Mod 100, "-00") Else AcctYear = Year(DateVal) & Format((Year(DateVal) + 1) Mod 100, "-00") End If End Function So in your query instead of using: DatePart("yyyy",[Status Date]) Use the following: AcctYear([Status Date],4,1) You'd then have to amend the criterion for the DSum function to: “Format([Status Date],”"yyyymm"”) >= “ & Format([Status Date],"yyyymm”) Note the pairs of contiguous quotes in the first of the Format function calls to represent literal quotes characters within the string. Ken Sheridan Stafford, England 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 -- Message posted via http://www.accessmonster.com
From: KenSheridan via AccessMonster.com on 19 Mar 2010 14:19 To restrict the rows returned add a WHERE clause: FROM qryResultsWon WHERE [Status] = “Won” GROUP BY …… Ken Sheridan Stafford, England -- Message posted via http://www.accessmonster.com
From: KenSheridan via AccessMonster.com on 19 Mar 2010 14:29 On second thoughts, you'd also need to include another column in the SELECT clause: Format([Status Date],"yyyymm”) As AYearMonth The expression would also need to be in the GROUP BY clause of course. The criterion for the DSum function call would then be: “Format([Status Date],”"yyyymm"”) >= “ & [AYearMonth] Ken Sheridan Stafford, England -- Message posted via http://www.accessmonster.com
From: KenSheridan via AccessMonster.com on 19 Mar 2010 14:32
That should have been: “Format([Status Date],”"yyyymm"”) <= “ & [AYearMonth] of course. Ken Sheridan Stafford, England -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1 |