Examples using SELECT

I’m a high school Economics teacher and I’m trying to create a simple bank system to teach my students how to manage their finances. I’ve successfully created a database that will update a running balance after multiple transactions, but I have to enter negative numbers in the withdrawal amount column. 

There has to be a way to word the following so it works...

Account.Balance is a formula field into which I put the following...

sum(transaction.amount where transaction.type is deposit) - sum(transaction.amount where transaction.type is withdrawal)

Note: Transaction.Type is a “choice” field with Deposit and Withdrawal. I have also put the numbers 1 and 2 into the above formula instead of Deposit, etc. 


I have also tried the following in the “Trigger after update” part of my Transaction sub-table:


if Type=1 then Accounts.Balance+Amount else if Type=2 then Accounts.Balance-Amount