Creating a running total calculated field

[nine]

I have a query, and I want a field that shows the current balance as of that transaction, eg:
_________________________________________________________
|__Transaction ID__|__Type_______|__Amount__|__Balance__|
|__1______________|__Deposit_____|__£10______|__£10_______|
|__2______________|__Deposit_____|__£30______|__£40_______|
|__3______________|__Withdrawal__|__£15______|__£25_______|

So far, I have this:

SELECT [Transaction ID], [Type], [Amount], (SELECT Sum([Amount]) FROM tblTransactions AS tblTransactions2 WHERE tblTransactions2.[Transaction ID] <= tblTransactions.[Transaction ID]) AS Balance
FROM tblTransactions
WHERE [Account ID]=1;

Although this does not look at the 'Type' field; it just adds the amounts; regardless of it being a deposit or withdrawal. I'm really not sure how to add this.

Thanks in advance.

 

[Rich]

It's easier to have either separate fields for credits and debits or to enter positive and negative values into a single field. Other than that you'll have to use Sum(If('s

 

[nine]

It's easier to have either separate fields for credits and debits or to enter positive and negative values into a single field. Other than that you'll have to use Sum(If('s

How would I go about using the If's? I understand what you are thinking of, i'm just not sure how I would go about implementing it.

 

[Rich]

Sum(Iif([Type]= "Deposit",[Amount],0))- Sum(Iif(Type]= "Withdrawal",[Amount],0))

 

[nine]

Thanks! I really appreciate this.

 

[nine]

Just one more thing, now I have had a chance to try this out...

I am using this query as the record source for a form, which is opened with a filter to select diffrent users. I put "WHERE [Account ID]=1" in as an example of one user. I really need to do this for diffrent users. So if it is opened up with [Account ID]=2, it would total all the amounts for that account. At the moment it totals all amounts, regardless of [Account ID]. Sorry that I diddnt mention this before, I thought i could work it out myself, turns out i couldn't.

 

[nine]

Anybody? Please, I need this.

 

[nine]

Any ideas would be greatly appreciated...

 

[Rich]

Easiest way is to pass the selected ID, usually via a combo box to the query.
In the criteria section of the ID field in your query something like
Forms!MyForm!MyCombo.

Look up parameter queries for further help on the subject

 

[nine]

Easiest way is to pass the selected ID, usually via a combo box to the query.
In the criteria section of the ID field in your query something like
Forms!MyForm!MyCombo.

Look up parameter queries for further help on the subject

Although this then only shows the the transactions carried out by that used, the calculation for the balance is still carried out on all transaction records, so the amount in transaction 4 is added to the totals from transactions 1 - 3, although transaction 4 was carred out by a diffrent Account ID than transactions 1- 3.

I think I need to use a SELECT DISTINCT in the Balance calucated field, but i'm not sure how to put it in.

 

[nine]

Please? I'm really not sure where to go from here.

 

[nine]

Anyone?

The problem (I think) is that the calculated field is run before the where condtion removes the records that I dont want to be included.


Thanks for any help.