Results 1 to 6 of 6
  1. #1
    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. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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
    Paul

  3. #3
    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. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Check the link again. Your criteria argument needs to include the field name the date is to be compared to.
    Paul

  5. #5
    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. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •