Running Sum in Query for Count Field

[maggioant]

I would like to create a running sum in a column of my query. The query is currently set up with fields:

Yr Mth PSI # * Status

The PSI # is a Count field, which counts the number of records within each year and month by their creation date. The * Status has a criteria of "New" so that only new records are counted. The output of the query is currently:

Yr Mth CountOFPSI # * Status
--- ----- ------------------ ----------
2009 7 3 New
2009 8 4 New
2009 9 10 New

How can I add a running sum column to achieve:

Yr Mth CountOFPSI # * Status RunningSum
--- ----- ------------------ ---------- ---------------
2009 7 3 New 3
2009 8 4 New 7
2009 9 10 New 17

I think I can do this using DCount, but I don't know how to create the expression. The name of the table this data is based on is "Data" and the name of the Query is "Count_New"

 

[Brianwarnock]

Why not use a report where the handling of running sums is simple.

Brian

 

[maggioant]

I need to create a graph based on the query.

I have my running total column as:

RunTot: DSum("PSI #","Data","DatePart('m' [* Creation Date]) <=" & [AMonth] & " And DatePart('yyyy',[* Creation Date])<=" & [AYear] & "")

But I keep getting #Error in the column. What could I be doing wrong?

 

[Brianwarnock]

I am not convinced that DSUM will give you a running sum, it will do a calculation and give that result on every row, but try this if you want, no gaurantees that the syntax is correct as it is not tested.

RunTot: DSum("PSI #","Data","DatePart('m' [* Creation Date]) <=’" & [AMonth] & "’ And DatePart('yyyy',[* Creation Date])<=’" & [AYear] & "’")

Brian

 

[Brianwarnock]

A quick search of the forum, didyou try it?, using the advanced search with running sum and the queries form indicates that it is a subquery not dsum that you require.

Brian

 

[tezread]

OK here is the latest - I have tried adding a custom field:

RunTot: DSum("EpisodeID","qry_cardiology1")

The query runs but the value in the running total is always the same '1298228'

?????