running sum in a query (access 2000)

Topic

New Reply

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;

Reply | Quote

Replies

I assume this is a sequel to post 301098. 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.

Reply | Quote

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]

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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]

Reply | Quote

I assume this is a sequel to post 301098. 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.

Reply | Quote