Running Total

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

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

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

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

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/For...ccess/201003/1

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

.

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

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@uwe wrote in message
news:a540998b61a83@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

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" wrote in message
...
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

.

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" wrote in message
...
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" wrote in message
...
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

.