Want to update previous records of a customer with current Record

[pervezjan]

Greeting


I need your help

I have problem to update previous entries of a Customer with Current Record of same Customer. my Table is as under
CustId. PrevBill. Afterduedate. date

If a customer pay his last bill then all the previous Entries of the Same customer must be changed = 0
I am using MDB
PrevBill = DMax("[AfterdueDate]", "[bill]", "[custNO]='" & [CustNo] & "'")
It is working but when a bill is paid. Then second time its bring the old value of any Record of the Customer as prevbill

If a customer pay his last bill then all the previous entries of the same must be = 0
any Idea.

Thanks

[June7]

I don't understand data structure.

Ideally, there would be a table of customers, a table of billings, a table of receipts. Calculate the customer net balance from the difference of the sums of billings and receipts.

[ohmydatabase]

I agree with @June7, I can't understand the problem you're running into.

Are you making a new record every time the customer submits a payment? If so, are you setting the PrevBill for that record as the maximum of the "AfterdueDate"? It seems more viable to have one record per customer with a current balance. You could then have a separate table to track all the transactions. You could write some code to automatically update a customer's next DueDate upon payment.