Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    running sum in a query (access 2000)

    I am trying to do a running sum in a query and have it done on each date (record), but unfortunately the value doesn't incrementally change on each date. Any ideas? SQL is below:
    SELECT [q-LiabFordMotorCredit 30].Date AS DateAlias, [q-LiabFordMotorCredit 30].VendorVendee, Sum([q-LiabFordMotorCredit 30].[Amount$]) AS [SumOfAmount$], [q-LiabFordMotorCredit 30].TransactionMemo, [q-LiabFordMotorCredit 30].TransTypeAbbrev, [q-LiabFordMotorCredit 30].SpendCatagory, [q-LiabFordMotorCredit 30].[Cleared-Rec], [q-LiabFordMotorCredit 30].Balance, DSum("Amount$","q-liabFordMotorCredit 30","") AS RunTot
    FROM [q-LiabFordMotorCredit 30]
    GROUP BY [q-LiabFordMotorCredit 30].Date, [q-LiabFordMotorCredit 30].VendorVendee, [q-LiabFordMotorCredit 30].TransactionMemo, [q-LiabFordMotorCredit 30].TransTypeAbbrev, [q-LiabFordMotorCredit 30].SpendCatagory, [q-LiabFordMotorCredit 30].[Cleared-Rec], [q-LiabFordMotorCredit 30].Balance;

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

    Re: running sum in a query (access 2000)

    I assume this is a sequel to <post#=301098>post 301098</post#>. If you look at the first link in my reply there, you will notice that you can use a DSum expression with a Where-condition as third argument. You have the following expression for the running sum: DSum("Amount$","q-liabFordMotorCredit 30",""). The third argument is empty, so the expression will return the same amount. I suggest that you study the MSKB articles in more detail first; if you still have questions then, come back here.

  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: running sum in a query (access 2000)

    Hi Hans,

    I have studied that article in detail and even downloaded the sample pack from MS TechNet. Yes, the where condition in this example is blank but trying to duplicate the example in the KB article I did in fact name the Date field as DateAlias and set up an expression in the WHERE condition asking it to return values where i.e [datealias] <= [date]

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: running sum in a query (access 2000)

    You have a number of fields in your GroupBy clause that will cause the sum to be for very small groups of records. If you want it to just sum by date, you have to remove any fields other than date in the GroupBy clause.
    Wendell

Posting Permissions

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