Running total

D

dmglenn

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
 
P

Pieter Wijnen

Simple Query will do

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

HTH

Pieter
 
D

dmglenn

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?
 
P

Pieter Wijnen

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
 
Top