Using running total in query to create graphs

[maggioant]

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

[maggioant]

Issue has been solved:

TotalClosed: DSum("[CountOfPSI #]","Total_Closed","[Mth]<=" & [Mth] & "")

[dcrake]

How you have not had any issues up till now I don't know. Using symbols such as * in your field names is fraught with danger. Rename them straight away.

David

[maggioant]

Oh, I've had plenty of issues. The problem is that the database is being used as a reporting tool for an online (eRoom) database. The field names have to match up with the export format of this database

[dcrake]

Then I suggest you create a new query that contains all the fields in the above table and give them alias names that comply with naming conventions and unreserved words.

Then use this query as the bases for all other queries, that way you will not fall foul of issues relating to naming conventions.

For example:

Field in table [* Creation Date]
Field in query CreationDate:[* Creation Date]

David