Calculated balance field

[nine]

I have a banking system, and I need to be able to show the balance as each transaction is made. This is what I have so far:

SELECT tblTransactions.lngTransactionID, tblTransactions.lngAccountID, tblTransactions.datDate, tblTransactions.txtType, tblTransactions.curAmount, (SELECT Sum(Iif([txtType] = "Deposit", [curAmount], 0)) - Sum(Iif([txtType] = "Withdrawal", [curAmount], 0)) FROM tblTransactions AS tblTransactions2 WHERE tblTransactions2.[lngTransactionID] <= tblTransactions.[lngTransactionID] = curAmount) AS Balance
FROM tblTransactions
WHERE (((tblTransactions.lngAccountID)=[AccID?]));

Although the WHERE clause is just for testing, in the real form, I am using a filter to only show records for a particular account.

The problem with this code (I think) is that the subquery is carried out before the main query's WHERE clause, so the balance is calculated on all records, not just the ones for a particular user - so if User A deposits £10, then User B deposits £10, User B's balance would show as £20, which is not the case. I have done some research, and I think the soloution might be in 'Correlated Subqueries', although I am finding learning about them a bit out of my depth.

Please help, thanks.

 

[GumbyD]

I think you will need a bit of code. I tried to replicate a bit of your database and create the query and code you will need. Take a look at the attached database and see if that can get you where you need to go.

 

[nine]

Thanks, but I'm not sure how you are trying to do this. I have attached a cut-down version of my database, with a few queries that I've tried:
qryTrans: The code I posted earlier. It works, but it carries out the balance for all users.
qryTrans2A&B: This does exactly what I need it to do, but is in two parts; qryTransB gets its infomation from qryTransA, I need it to get it straight from the table.
qryTrans3: My attempt at merging qryTrans2 together, but I cannot seem to get it to work.

Thanks for any help.

 

[GumbyD]

I took a look at your database. If you add this function to a new module and then run use the query below I think it will give you what you want.

code for new module

Option Compare Database
Option Explicit

Public Function CalcBal(AcctNum As Long, LDate As Date) As Currency
Dim sql As String
Dim rstBal As Recordset


sql = "SELECT tblTransactions.lngAccountID, Sum(IIf([txtType]='Deposit',[curAmount],-[curAmount])) AS FixedAmt " & _
"FROM tblTransactions " & _
"WHERE (((tblTransactions.datDate) <= #" & LDate & "#)) " & _
"GROUP BY tblTransactions.lngAccountID " & _
"HAVING (((tblTransactions.lngAccountID)=" & AcctNum & "));"
Set rstBal = CurrentDb.OpenRecordset(sql, dbOpenSnapshot)
If rstBal.EOF Then
CalcBal = 0
Else
CalcBal = rstBal!FixedAmt
End If
rstBal.Close


End Function


SQL for query:

SELECT tblTransactions.lngTransactionID, tblTransactions.lngAccountID, tblTransactions.datDate, tblTransactions.txtType, tblTransactions.curAmount, CalcBal([lngAccountID],[datDate]) AS RunningBanance
FROM tblTransactions;

Good luck

 

[nine]

Ok, I've had ago with that, and it doesnt quite work properly. When I tried it with account 1, I got this:
£0.00
£30.00
£20.00
£10.00

When I should have got this:
£10.00
£30.00
£20.00
£30.00

and I'm not sure how you got that result.

 

[pbaldy]

You don't need code, and you were close with your first query. You just needed to add the account ID to the where clause. See if this does what you want:

SELECT tblTransactions.lngTransactionID, tblTransactions.lngAccountID, tblTransactions.datDate, tblTransactions.txtType, tblTransactions.curAmount, (SELECT Sum(Iif([txtType] = "Deposit", [curAmount], 0)) - Sum(Iif([txtType] = "Withdrawal", [curAmount], 0)) FROM tblTransactions AS tblTransactions2 WHERE tblTransactions2.[lngTransactionID] <= tblTransactions.[lngTransactionID] AND tblTransactions2.lngAccountID = tblTransactions.lngAccountID) AS Balance
FROM tblTransactions;

 

[nine]

You don't need code, and you were close with your first query. You just needed to add the account ID to the where clause. See if this does what you want:

SELECT tblTransactions.lngTransactionID, tblTransactions.lngAccountID, tblTransactions.datDate, tblTransactions.txtType, tblTransactions.curAmount, (SELECT Sum(Iif([txtType] = "Deposit", [curAmount], 0)) - Sum(Iif([txtType] = "Withdrawal", [curAmount], 0)) FROM tblTransactions AS tblTransactions2 WHERE tblTransactions2.[lngTransactionID] <= tblTransactions.[lngTransactionID] AND tblTransactions2.lngAccountID = tblTransactions.lngAccountID) AS Balance
FROM tblTransactions;

It works flawlessly. Thank you very much!! You have no idea how crazy this was driving me, and, of course, the solution was painfully simple . I really appreciate the help you, and GumbyD gave me. Thanks!