Running Balance

[Abdul Shakeel]

Hi,

I have a query in which I have fields TransactionID, AmountDeposit,
AmountWithdraw I want to make an other field named Balance which shows
diffrence of AmountDeposit, AmountWithdraw how could I do this.

 

[RonaldoOneNil]

With your query in design view, just type this into the field row of your
next available column

Balance: [AmountDeposit]-[AmountWithdraw]

 

[Abdul Shakeel]

Its just give me the difference of only one transaction however I want
running balance e.g.
TransactionID Amount Deposit AmountWithdraw Balance

1. 1500.00
1500.00
2. 1500.75
3000.75
3. 1200.00
1800.75

With your query in design view, just type this into the field row of your
next available column

Balance: [AmountDeposit]-[AmountWithdraw]

Hi,

I have a query in which I have fields TransactionID, AmountDeposit,
AmountWithdraw I want to make an other field named Balance which shows
diffrence of AmountDeposit, AmountWithdraw how could I do this.

 

[RonaldoOneNil]

Sorry, try this instead substituting your table name where I have put "Trans"

Balance: CCur(DSum("[AmountDeposit]","Trans","[TransactionID] <=" &
[TransactionID])-DSum("[AmountWithdraw]","Trans","[TransID] <=" & [TransID]))

Its just give me the difference of only one transaction however I want
running balance e.g.
TransactionID Amount Deposit AmountWithdraw Balance

1. 1500.00
1500.00
2. 1500.75
3000.75
3. 1200.00
1800.75

With your query in design view, just type this into the field row of your
next available column

Balance: [AmountDeposit]-[AmountWithdraw]

Hi,

I have a query in which I have fields TransactionID, AmountDeposit,
AmountWithdraw I want to make an other field named Balance which shows
diffrence of AmountDeposit, AmountWithdraw how could I do this.

 

[RonaldoOneNil]

Sorry again, last bit should be TransactionID as well

Balance: CCur(DSum("[AmountDeposit]","Trans","[TransactionID] <=" &
[TransactionID])-DSum("[AmountWithdraw]","Trans","[TransactionID] <=" &
[TransactionID]))

Its just give me the difference of only one transaction however I want
running balance e.g.
TransactionID Amount Deposit AmountWithdraw Balance

1. 1500.00
1500.00
2. 1500.75
3000.75
3. 1200.00
1800.75

With your query in design view, just type this into the field row of your
next available column

Balance: [AmountDeposit]-[AmountWithdraw]

Hi,

I have a query in which I have fields TransactionID, AmountDeposit,
AmountWithdraw I want to make an other field named Balance which shows
diffrence of AmountDeposit, AmountWithdraw how could I do this.

 

[John Spencer]

Assumption: TransactionID is sequential and unique. If it is not, you need
some other way to determine the order of the transactions.

SELECT A.TransActionID, A.[Amount Deposit], A.AmountWithdraw
, Nz(Sum(B.[Amount Deposit],0) - Nz(Sum(B.AmountWithdraw),0) as Balance
FROM [YourTable] as A INNER JOIN [YourTable] as B
ON A.TransactionID >= B.TransactionID
GROUP BY A.TransActionID, A.[Amount Deposit], A.AmountWithdraw

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County