# Thread: Another Running Sum Problem

1. Registered User
Join Date
Mar 2011
Posts
3

## Unanswered: Another Running Sum Problem

First of all, let me say that I am entirely self-taught in MSAccess and know nothing about SQL, so I appreciate patience with any potentially dumb questions...

I have the following table in MS Access:
Item Date Seq Qty
0080-2301 1/1/2001 Bal 1407
0080-2301 3/7/2011 Req-MFG -280
0080-2301 3/14/2011 Req-MFG -400
0080-2301 3/21/2011 Req-MFG -80
0080-2301 3/28/2011 Req-FPO -80
0080-2301 4/4/2011 Req-FPO -120
0080-2301 4/11/2011 Req-FPO -120
0080-2301 4/11/2011 Sup-PO 1000
0080-2301 4/18/2011 Req-FPO -120
0080-2301 4/25/2011 Req-FPO -120
0080-230 1 5/2/2011 Req-FPO -120
0080-2301 5/9/2011 Req-FPO -120
0080-2301 5/16/2011 Req-FPO -120
0080-230 1 5/23/2011 Req-FPO -120
0080-2301 5/30/2011 Req-FPO -120

I would like to calculate the running sum by date. How do I do it?

2. Registered User
Join Date
May 2005
Location
Posts
2,888
Since you titled the thread "Another..." I assume you've seen other threads on running sums. Thus I assume that you're issue is that you're using date where others may have used an ID field or something. In that instance, the syntax for a date is here:

General: DLookup Usage Samples

3. Registered User
Join Date
Mar 2011
Posts
3
This gives me a total of -353 which is correct, but not what I'm looking for. The running sum results that I'm looking for are: 1/1/01=1407, 3/7/11= 1127, 3/14/11 = 707, 3/21/11 = 647, etc.

Here is my sql:

SELECT [Analysis Summary Query].Item, [Analysis Summary Query].Date, [Analysis Summary Query].Seq, Sum([Analysis Summary Query].SumOfQty) AS SumOfSumOfQty, DSum("sumofqty","analysis summary query",#3/21/2001#) AS RunningSum
FROM [Analysis Summary Query]
GROUP BY [Analysis Summary Query].Item, [Analysis Summary Query].Date, [Analysis Summary Query].Seq;

Note: I get the same -353 result regardless of whether I put in a [date] or an actual #date#

4. Registered User
Join Date
May 2005
Location
Posts
2,888
Check the link again. Your criteria argument needs to include the field name the date is to be compared to.

5. Registered User
Join Date
Mar 2011
Posts
3
I guess I don't understand...

and to clarify, I'm trying to get in effect a net running sum by date where the new balance consists of the old balance plus what ever adjustments are listed for a date. Kind of an MRP scenario.

6. Registered User
Join Date
May 2005
Location
Posts
2,888

DSum("sumofqty","analysis summary query","[Date] = #3/21/2001#")

By the way, "Date" is a bad name for a field, because sooner or later Access will confuse it with the Date() function.