Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Total in a Query (A97 SR-2)

    I am trying to calculate a running sum in a query as listed below.

    Amount RunningTotal
    1 1
    2 3
    3 6
    4 10

    I have tried using DSum as follows, but it results in the RunningTotal calculation being 10 in each row.

    RunningTotal: DSum("[Amount]","tblData")

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Total in a Query (A97 SR-2)

    You need a unique key by which your data are ordered to do this.
    If your key field is named ID and the field you want to sum is Amount, the expression for Running Total would become

    DSum("Amount", "tblData", "ID<=" & [ID])

    This means that DSum is taken over all records with an ID up to and including the ID in the current record.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Total in a Query (A97 SR-2)

    Thank you very much! I already had the ID field in the table and your solution worked great.

Posting Permissions

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