DataBase - Running Total

Asked By Neil

19-Mar-10 12:46 PM

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

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] &
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

19-Mar-10 06:10 PM

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


--
Message posted via http://www.accessmonster.com

19-Mar-10 06:19 PM

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

19-Mar-10 06:29 PM

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

19-Mar-10 06:32 PM

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

19-Mar-10 02:51 PM

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.

19-Mar-10 07:55 PM

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.


--
Message posted via http://www.accessmonster.com

19-Mar-10 04:34 PM

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
cannot get to work, also tried your last post

Neil.

19-Mar-10 04:55 PM

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

19-Mar-10 06:01 PM

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

20-Mar-10 12:14 AM

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???)
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 do not 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


--
Message posted via http://www.accessmonster.com

19-Mar-10 10:51 PM

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.

20-Mar-10 04:49 AM

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

20-Mar-10 11:55 AM

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

20-Mar-10 04:26 PM

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???)
AND [Status] = ???Won??? AND
AcctYear([RW2.Status Date],4,1) =
AcctYear([RW1.Status Date],4,1))

Thanks for your help so far.

Neil

20-Mar-10 09:07 PM

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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

20-Mar-10 06:29 PM

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

21-Mar-10 05:51 AM

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

Neil

21-Mar-10 04:44 PM

Neil:

I cannot 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 have 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 have 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",
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 have 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 have not formatted the values as currency, but
that is 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 will 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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201003/1

21-Mar-10 04:10 PM

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] &
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] &
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.