Running Total

[Neil]

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

 

[KARL DEWEY]

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]);

 

[Neil]

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).

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

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

 

[Neil]

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

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.

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

.

 

[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

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

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.

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

.

 

[KARL DEWEY]

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.

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

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

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.


:

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

.

.

 

[Neil]

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

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.

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

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

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.


:

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

.

.

 

[Neil]

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

 

[Neil]

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
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");

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

 

[Neil]

Have worked it out, will post sql tomorrow when I have tidied it up, thanks
to everyone for your help.

Neil

 

[Neil]

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.

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

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