Complicated running balance query, if possible?

[wolves]

Before I attempt to write a program for this, has anyone ever tried to keep a running balance on a query, and at the same, change the balance fields, for instance:

INPUT DATA:

NAME      AMT        ID
JOE       125.00     1
JOE       125.00     2
JOE       200.00     A
JOE       100.00     B

The goal is to take JOE, ID 1, subtract that amount from JOE ID A, giving JOE ID A new balance of 75.00. Since JOE ID A still has a balance, then subtract JOE ID 2, from new balance in JOE ID A, which would ZERO out JOE ID A, plus leave a negative balance of -50. I would need to take the new JOE A, add that into JOE B, to reduce JOE B to 50.

So the outcome should now look like:

NAME      AMT        ID
JOE       125.00     1
JOE       125.00     2
JOE         0.00     A
JOE        50.00     B

Maybe too complicated for Access. Just wondering.

Thanks in advance.

 

[ZmrAbdulla]

I don't understande what are the IDs that keep on changing. Is that a different person? or the recordID?

Anyway here is a sample from Microsoft that has running sum in query.

http://office.microsoft.com/en-us/assistance/HA011201381033.aspx

All the three sample downloads have too many examples.

________________________________________________________
Zameer Abdulla
Help to find Missing people
My father was a realistic father; not a vending machine dispense everything I demanded for!!

 

[lespaul]

plus storing calculated values breaks normalization rules.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[wolves]

Thanks for the help. That Microsoft link gave me an idea in PL/SQL where I got this thing to work.

Thanks again,