Running sum expression

  • Thread starter Thread starter Opal
  • Start date Start date
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(DateAdd("m",-1,Date()),"yyyymm") And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-
IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) =
Format(DateAdd("m",-1,Date()),"yyyymm") AS RunningSum
 
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!

email address is invalid
Please reply to newsgroup only.
 
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!

email address is invalid
Please reply to newsgroup only.



Opal said:
I am trying to write an expression that will show a running
sum.
Click to expand...
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.
Click to expand...
I have been trying to write it and can't seem to quite get what
I want.
Click to expand...
I have the following to show total Open and Closed:
Click to expand...
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;
Click to expand...
Would the expression I want be something like:
Click to expand...
Sum((IIf(Format([ProblemDate],"yyyymm")=[ProbYearMonth],1,0)+
IIf([ProbYearMonth]>Format([ProblemDate],"yyyymm") =
Format(DateAdd("m",-1,Date()),"yyyymm") And
(Format([ClosedDate],"yyyymm")>=[ProbYearMonth]),1,0))-
IIf([ProbYearMonth]=Format([ClosedDate],"yyyymm"),1,0)) =
Format(DateAdd("m",-1,Date()),"yyyymm") AS RunningSum- Hide quoted text-
Click to expand...

- Show quoted text -
Click to expand...

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