Running Total
in [General]
|
Prev: Multiple listbox questions
Next: Filter using query
From: KARL DEWEY on 19 Mar 2010 14:51 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 19 Mar 2010 15:55 On third thoughts, I think I was right the first time with the criterion for the DSum function call: “Format([Status Date],”"yyyymm"”) >= “ & Format([Status Date],"yyyymm”) I'd thought at first you were self referencing the current query, but I see now that it's a separate query. KenSheridan wrote: >That should have been: > >“Format([Status Date],”"yyyymm"”) <= “ & [AYearMonth] > >of course. > >Ken Sheridan >Stafford, England -- Message posted via http://www.accessmonster.com
From: Neil on 19 Mar 2010 16:34 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. "KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote in message news:a540998b61a83(a)uwe... > 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: Neil on 19 Mar 2010 16:55 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 19 Mar 2010 18:01
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 >>> >>> . >>> |