var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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?
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
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#
Check the link again. Your criteria argument needs to include the field name the date is to be compared to.
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.
To follow your test:
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.
Tags for this Thread