Running total

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

Guest

I have a table with 3 fields, AccountNumber, Security Number, and LotShares.
I would like to create a query that calculates a running total of lotshares
by security by accountnumber.
Multiple accounts will hold the same security. Within each security held by
a fund there may be multiple lots.

I am a novice and would rather avoid VB if possible.

Have tried DSUM with limited success.
Thanks in advance
 
Simple Query will do

SELECT AccountNumber, SecurityNumber, Sum(LotShares) As Total
FROM MyTable
Group By AccountNumber, SecurityNumber

HTH

Pieter
 
Pieter
Thanks for responding but I'm not sure that will do what I'm looking for.

I need a running total. So if my data is this:

Account Security LotShares
ABC 123 10
ABC 123 20
ABC 123 75
ABC 456 20
ABC 456 80

I would like output like this:

Account Security LotShares RunTotal
ABC 123 10 10
ABC 123 20 30
ABC 123 75 105
ABC 456 20 20
ABC 456 80 100

Any thoughts?
 
Then Try

SELECT A2.ID, A2.ACC, A2.SEC ,A2.LS,A2.LS + nz((SELECT SUM(A4.LS) AS LST
FROM A AS A4
WHERE A4.ACC=A2.ACC AND A4.SEC=A2.SEC AND A4.ID < A2.ID),0) As RSum
FROM A As A2

Note that I Assume the "ordering" is based on a Unique key (ID)

Pieter
 
Back
Top