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(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
 
Ad

Advertisements

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!

email address is invalid
Please reply to newsgroup only.
 
Ad

Advertisements

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!

email address is invalid
Please reply to newsgroup only.



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(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-

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

New to complex charts & struggling 11
Totals query rework 1
Query not return all records 1
Multi table query 1
Calculate MTD and YTD in a query 8
Summing an Expression 6
Union query error 3
SQL too big ?? 0

Top