# Running sum expression

O

#### Opal

I am trying to write an expression that will show a running
sum.

The expression should show the total number of the
open issues from the previous month plus all new issues
from the current month minus all closed issues from the
current month.

I have been trying to write it and can't seem to quite get what
I want.

I have the following to show total Open and Closed:

SELECT qryProblemYrMn.ProbYearMonth AS Expr1,
Sum(IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)) AS [#
Opened],
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS [#
Closed]
FROM Problem, qryProblemYrMn
GROUP BY qryProblemYrMn.ProbYearMonth
ORDER BY qryProblemYrMn.ProbYearMonth;

Would the expression I want be something like:

Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)+
IIf([ProbYearMonth]>Format([ProblemDate],"yyyymm") =
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-
IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) =

D

#### Dale Fye

What do you want to do with your results?

Display them on a form, a report?

If on a report, you can use the running sum property of a textbox in your
report.

How about open issues from the month before last, or the month before that?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

O

#### Opal

What do you want to do with your results?

Display them on a form, a report?

If on a report, you can use the running sum property of a textbox in your
report.

How about open issues from the month before last, or the month before that?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

Opal said:
I am trying to write an expression that will show a running
sum.
The expression should show the total number of the
open issues from the previous month plus all new issues
from the current month minus all closed issues from the
current month.
I have been trying to write it and can't seem to quite get what
I want.
I have the following to show total Open and Closed:
SELECT qryProblemYrMn.ProbYearMonth AS Expr1,
Sum(IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)) AS [#
Opened],
Sum(IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) AS [#
Closed]
FROM Problem, qryProblemYrMn
GROUP BY qryProblemYrMn.ProbYearMonth
ORDER BY qryProblemYrMn.ProbYearMonth;
Would the expression I want be something like:
Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)+
IIf([ProbYearMonth]>Format([ProblemDate],"yyyymm") =
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-
IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) =
Format(DateAdd("m",-1,Date()),"yyyymm") AS RunningSum- Hide quoted text-

- Show quoted text -

Actually they will appear in a chart so I need to be able to plot the
value as it
increases from month to month.