running sum by date 1

[shelspa]

I have the following as a field in a query to show a running sum (taken from another thread):

RunningSum: DSum(&quot;[YearlySavings]&quot;,&quot;tblMaster&quot;,&quot;[ID]<=&quot; & [ID])

I want this to use the field &quot;Proposed Completion&quot; which is a date rather than the ID field for the order. I would appreciate any ideas on this.

 

[Isadore]

shel: not quite clear what you need. Could you provide additional information.

 

[shelspa]

This is what I have for the output:

YearlySavings RunningSum
100 100
200 300
200 500
0 500
100 600

It is using the ID field for the ordering of the additions. I want it to use &quot;Proposed Completion&quot; which is a date. This has to be done in a query and not a report so I can chart the data.

 

[scriverb]

I believe this should do it. I am assuming that Proposed Completion is a date/time field. This will take the current records Proposed Completion date and match it against all the records in the same table and select only those that are <= to the same date. Thus a running sum:
RunningSum: DSum(&quot;[YearlySavings]&quot;,&quot;tblMaster&quot;,&quot;[Proposed Completion]<=&quot; & [Proposed Completion])
Let me know if you have any problems with this.
Bob Scriver

 

[Isadore]

Thanks scriverb.

 

[shelspa]

That gives &quot;syntax error missing operator&quot;

 

[scriverb]

Please post your DSum function statement as it is in your query so that I may see it. You could post the entire SQL also. Bob Scriver

 

[shelspa]

RunningSum: DSum(&quot;[YearlySavings]&quot;,&quot;tblMaster&quot;,&quot;[ID]<=&quot; & [ID])

 

[scriverb]

What you posted was your origianl DSUM that you picked up from another thread. Your post said you wanted to use [Proposed Completion] which is a date for the selection criteria. The statment that I gave you is as follows:

RunningSum: DSum(&quot;[YearlySavings]&quot;,&quot;tblMaster&quot;,&quot;[Proposed Completion]<=&quot; & [Proposed Completion])

Which one did you use? Get back with me on this one. I will be away for about 1 hr. but I will respond after that. Bob Scriver

 

[shelspa]

The first works great using my &quot;ID&quot; autonumber field but the sums are not calculated in the order that I want which is by the field &quot;Proposed Completion&quot;. This is a date field. The sums should be added by this field in ascending order. Replacing &quot;ID&quot; with &quot;Proposed Completion&quot; produces a syntax error missing operator.

 

[scriverb]

Try this:

RunningSum: DSum(&quot;[YearlySavings]&quot;,&quot;tblMaster&quot;,&quot;[Proposed Completion] <= #&quot; & [Proposed Completion] & &quot;#&quot

Let me know how this works.
Bob Scriver

 

[shelspa]

That did it. Great job. Thank you!

 

[scriverb]

You are welcome. Glad to be of assistance. Bob Scriver