[Advanced]Question about adding new record into two tables

[kan8]

Hello, I am a newbie in this forum.
Recently, I am helping my friend to write a simple accounting software based on Access. I have finished most of the neccessary tables, forms and reports by following the Microsoft Sample Database (NorthWind).
The following is my difficulties:
I want to have another table (bank) to store up
1.the amount of each customer need to pay by checking each order's customerID
2. The amount the customer has paid so far, so i can know how much he/she still need to pay
In order to update this table, once there is new order added in the order table, i need to know that and update it, but how? I have used a form and subform to input the order detail, and the order table is automatically updated once data is input. How can i trigger the update of the "bank" table??

 

[kan8]

Actually I want to calculate in the way same as the way of bank. That is i can show the detail based on date.
Like
Date TransactionAmount Balance
20/6/2005 400,000 400,000 'New order submitted
21/6/2005 -100,000 300,000 '100,000 paid
22/6/2005 -300,000 0 '200,000 paid
26/6/2005 500,000 500,000 'New order submitted


Any way to do it?

 

[ThreeCrow]

How can i trigger the update of the "bank" table??

You do not want to do this, instead use Queries to calculate your totals.
In the Northwind Database goto queries you'll see one call Invoice Totals.
Duplicated this in your database, changing Field Names where you need to.

Create another Table and Name it CustomerRemits (or Something like that).
Field: CutomerReimtID, DateEntered, InvoiceNumber, RemitAmount.

Create a Group by Query based on this table, useing only the Fields "InvoiceNumber" GroupBy this one and "RemitTotal:RemitAmount" Sum this one. Name this query InvoiceRemitTotals.

Create another query with the following
table SalesOrder.InvoiceNumber
query InvoiceTotals.InvoiceTotal
query InvoiceRemitsTotals.RemitTotal
in the next emty field put this calculation
BalanceDue:InvoiceTotal-RemitTotal
Name this query BalanceOnInvoice

This should what you want, anything that you can calculate you do not want to store.