simple update query (I hope)

[p5x]

In my make loan form I am showing the charge due for that particular loan and the charge forany previous, unpaid loans (ie-debt) made by a member. I want to add the debt to the current loan fee and have that toal replace the value that was previously in my debt field.

so say if member 3 has debt of £10 and then borrows an item costing £1, I want the debt (in the member table) to go to £11.
I carry on getting some cyclic error or something everytime i try this, i'm sure it's pretty simple I just can't figure it out.

Thanks in advance

 

[Jack Cowley]

You do not want to store that bit of data but calculate it as you need it. You can have a members table and then a table that you enter payments and loans. Now you can use a query to add the amount loaned minus the payments and you have their current balance. The loan/payment table is for all members and the MemberID is the Foreign key for this table....

hth,
Jack

 

[p5x]

sorry if i'm missing something here but how will i calculate the debt for each customer every time?
Are you saying in the member table have a field for all the loan charges and a field for all the payments made?
How would you add a new charge to the previous charge?

 

[Rich]

In simple terms you need three tables to deal with this structure
TblCustomer, customer details etc
TblLoans loan details
TblPayments payment details
TblCustomer has a one to many rel with TblLoans, CustID is the FK
You can then depending on how you want to relate payments either have CustID or LoanID as the foreign key in TblPayments

 

[Jack Cowley]

P5X -

There is a sample program called Ledger that comes with Access. Take a look at the Transactions table that comes with that db. That is what you are basically after. Every time a payment is made you record that payment in a new record for that member. Every time a new loan is made you enter the amount in in the Loan field of a new record. Since each record is related to the member you can use a query to put in the members ID as criteria and then you will see all of their payments and loans and you can calculate the balance in the query. So a simple query will always show you the current balance for any selected member.

hth,
Jack