Sum and update query

[snoopy92211]

I have a table, Loans.

Fields:
ID (key)
Loan Number
Principal Balance.

I have another table, Transaction, that's based off the Loan Number field on the loans table.

Fields:
LoanNumber
Payment Type (2 choices, payment and advance)
Amount

I have a user form where users can specify the payment type, and amount, based on a certain loan number.

I'm attempting to do this:

User enters a transaction into the database. once the transaction is entered, the Principal Balance field for the specified loan number is updated.

If the payment type is advance, the query will subtract the amount from principalbalnce. if payment, the query wll add the amount to the principal balance.


Here's what I have sofar.

The userform updates the transactions table with the information correctly.

My next step is to update the principal balance.

I'm thinking I could use some sort of update query, but I don't know where to begin.

HELP!!

 

[lespaul]

have you read the document linked below? Storing calculated values breaks normal form. The traditional way to get a calculated value is in a query (take the beginning balance and subtract the payments resulting in the "new balance").



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[snoopy92211]

Thanks for the tip. Can you please give some instruction on how I would create the query? An example would help greatly.

much appreciated!

snoopy92211