Re: Conditional Running Sum

Hello Arch,
You are right that i did a mistake in the first line.

your statement CORRECT! Start date but not the
Finish date?>

Regardless how i define "between"-including or does not including
Start Date-, i can not get proper output.
May be i could not describe my problem well to you.
If you are interested, i am posting variations below with its output:

Variation1:
SELECT Query1.SortedDate, DSum("Money","Query1","[SortedDate] >= #" &
[Start] & "#" And "[SortedDate] < #" & [Finish] & "#") AS RunningMoney
FROM Query1
ORDER BY Query1.SortedDate;
***
Output for Variations1:
SortedDate RunningMoney
01/01/2008 13
02/01/2008 13
03/01/2008 13
04/01/2008 13
05/01/2008 13

Variation2: /Query maker approach
SELECT Query1.SortedDate, (SELECT Sum([Money])
FROM [Query1] AS [qry_1] WHERE [qry_1].[SortedDate]>=
[Query1].[Start] AND [qry_1].[SortedDate]<[Query1].[Finish]) AS
RunningMoney
FROM Query1
ORDER BY Query1.SortedDate;
***
Output for Variations2:
SortedDate RunningMoney
01/01/2008 10
02/01/2008 NULL
03/01/2008 12
04/01/2008 3
05/01/2008 NULL

Re: Conditional Running Sum

On Sun, 30 Mar 2008 22:55:30 -0700 (PDT), mezzanine1974
wrote:

>Hello Arch,
>You are right that i did a mistake in the first line.
>
>your statement CORRECT! >Start date but not the
>Finish date?>
>

It probably can be done in SQL, but I'm not good enough in SQL to do
it. This function works as you requested, however:


Public Function CalcSum(Tdate As Date)
Dim SQL As String
Const StartDate As String = "Sdate"
Const FinishDate As String = "Fdate"
SQL = "Select sum(money) from q1 " & _
"where #" & Tdate & "# >= " & StartDate & _
" AND #" & Tdate & "# < " & FinishDate
CalcSum = CurrentProject.Connection.Execute(SQL).GetString
CalcSum = Left(CalcSum, Len(CalcSum) - 1)
End Function



SELECT SortedDate,
CalcSum(SortedDate) as RunSum
FROM Query1 ;

SortedDate RunSum
1/1/2008 1
2/1/2008 1
3/1/2008 10
4/1/2008 12
5/1/2008

-Arch