Updata data from into a table

[imichitterman]

I have to perform " a not so clear transaction" .
The situation is like this:
I am trying to mimic an ATM machine operations.
When the customer enters his PIN and gets in
he wants to do a transaction ( deposit or widthdraw)
I want to make sure that his Balance is updated, that is, a sum is added too or subtracted from the "balance on hand".

So i had a table like this
CustomerPIN, Balance, TransactionAmount, TransactionType.
1234, 500, 200, 1

the 1 means that he has deposit.

now this customer comes back to do another deposit, how do i achieve getting his balance to change from the "500" to the new figure.

 

[antomack]

To do what you ask would require the use of an update query to recalculate the balance but overall your current design may not be best suited to the situation.

UPDATE YOURTABLE SET Balance = Balance + iif(TransactionType = 1,TransactionAmount, TransactionAmount * -1)

Possible Better Design
Since money transactions of any sort are pretty important you should really be saving the transaction data for analysis if any queries arise. Thus you should store the transactions in their own table. At the same time since you now have a record of all the transactions processed you should not save your recalculated balance since you have the data to calculate it on the fly once you store an initial opening balance for the customer.

Thus you should use at least 2 tables as follows
Customer
CustomerID, OpeningBalance
Transactions
TransactionID (autonumber?), CustomerID, TransactionDate, TransactionTime, TransactionAmount, TransactionType, DepositVerified
I've included a DepositVerified field so you can flag whether or not a deposit amount was correct as verified by branch staff, thus ignoring it from balance calculations until it is verified.

You can then set up a query to calculate the total of all (verified) transactions that have occured up to any particular date and add this value to the OpeningBalance to give your current balance.