Graph based on running totals

maggioant

Registered User.
Local time
Today, 01:06
Joined
Sep 30, 2009
Messages
15
I need to create a graph that shows the cumulative number of Open vs. Closed issues on a month-to-month basis in an issue tracking database.

The data table has the fields [* Creation Date] [Closed Date] [* Status] [PSI #]

I have created 2 queries (one for open issues and one for closed issues) that group by the two date columns:
Yr: Year([* Creation Date]) & Mth: Month([* Creation Date])
Yr: Year([Closed Date]) & Mth: Month([Closed Date])

The third column is a critera of [* Status] for New or Closed.

The fourth column is a Count of [PSI #] which becomes [CountOfPSI #] in the query.

I tried to create a running total in the 5th column, but instread of giving me a running total, the query just multiplies the [CountOfPSI #] field by 3. I can't figure out why!

This problematic 5th column is an expression:
ClosedTotal: DSum([CountOfPSI #],"New_Count",[* Status]="New")

Any ideas how I can make this 5th column a running total of the [CountOfPSI #] field? I'm listing the Query full SQL below for further reference:

SELECT Year([* Creation Date]) AS Yr, Month([* Creation Date]) AS Mth, Data.[* Status], Count(Data.[PSI #]) AS [CountOfPSI #], DSum([CountOfPSI #],"New_Count",[* Status]="New") AS ClosedTotal
FROM Data
GROUP BY Year([* Creation Date]), Month([* Creation Date]), Data.[* Status]
HAVING (((Data.[* Status])="New"))
ORDER BY Data.[* Status];
 

Users who are viewing this thread

Back
Top Bottom