Running Sum in Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can a running sun be written in a query? I have done running sums in reports
but don't know how to do it in a query. I am trying to have a query that
reflects a running sum for a checking balance - the query would show the
balance in a checking account calculating the deposit and withdrawal
transactions. I am not an expert in access so if this can be done can you
please explain how in detail so I can understand.

Thanks to all
 
Snip,

You can do this in a query with a domain aggregate function. The details
of this will depend on how your data is structured. As an example,
let's say you have an Amount field, a TransactionType field whose value
is 1 for a deposit and -1 for a withdrawal, and a TransactionDate field.
So...
RunningBalance:
DSum("[Amount]*[TransactionType]","YourTable","[TransactionDate]<= #" &
[TransactionDate] & "#")

If you have a very large number of transaction records, this query will
probably be sluggish.
 
Snip,

The answer given by Steve will only work to an extent... Here is the
problem, you may have more than one transaction that occurs on the same date.
This most likely, so considering the date will not give you an accurate
running total. Instead you will want to assign a sequential transaction
number to each record. Then in Steve's sql expample replace the [date] with
the [transaction number].

Best of luck!

Steve Schapel said:
Snip,

You can do this in a query with a domain aggregate function. The details
of this will depend on how your data is structured. As an example,
let's say you have an Amount field, a TransactionType field whose value
is 1 for a deposit and -1 for a withdrawal, and a TransactionDate field.
So...
RunningBalance:
DSum("[Amount]*[TransactionType]","YourTable","[TransactionDate]<= #" &
[TransactionDate] & "#")

If you have a very large number of transaction records, this query will
probably be sluggish.

--
Steve Schapel, Microsoft Access MVP
Can a running sun be written in a query? I have done running sums in reports
but don't know how to do it in a query. I am trying to have a query that
reflects a running sum for a checking balance - the query would show the
balance in a checking account calculating the deposit and withdrawal
transactions. I am not an expert in access so if this can be done can you
please explain how in detail so I can understand.

Thanks to all
Click to expand...
Click to expand...
 
Back
Top