|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 6th, 2009, 08:53 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Running Balance
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. 
|
|
#2
October 6th, 2009, 09:25 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Running Balance
With your query in design view, just type this into the field row of your
next available column Balance: [AmountDeposit]-[AmountWithdraw] "Abdul Shakeel" wrote: 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.
|
|
#3
October 6th, 2009, 09:43 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Running Balance
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 "RonaldoOneNil" wrote: With your query in design view, just type this into the field row of your next available column Balance: [AmountDeposit]-[AmountWithdraw] "Abdul Shakeel" wrote: 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.
|
|
#4
October 6th, 2009, 10:12 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Running Balance
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])) "Abdul Shakeel" wrote: 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 "RonaldoOneNil" wrote: With your query in design view, just type this into the field row of your next available column Balance: [AmountDeposit]-[AmountWithdraw] "Abdul Shakeel" wrote: 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.
|
|
#5
October 6th, 2009, 10:16 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Running Balance
Sorry again, last bit should be TransactionID as well
Balance: CCur(DSum("[AmountDeposit]","Trans","[TransactionID] =" & [TransactionID])-DSum("[AmountWithdraw]","Trans","[TransactionID] =" & [TransactionID])) "Abdul Shakeel" wrote: 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 "RonaldoOneNil" wrote: With your query in design view, just type this into the field row of your next available column Balance: [AmountDeposit]-[AmountWithdraw] "Abdul Shakeel" wrote: 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.
|
|
#6
October 6th, 2009, 02:05 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Running Balance
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 Abdul Shakeel wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
