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];