update a specific field of table

[Guest]

in my database, i got
an invoice table with field invoice_no, client_no, total
a job table with job_no, job_amount, invoice_no
a part table with part_no, quantity, unit_price, invoice_no

the logic is every time i got a new transaction, i create an invoice no, and
insert this to the invoice table, so now in the table, i got the invoice no
and client no.
for the total amount in the invoice, it equals to the sum of the job_amount
and part_total. so i hav created 2 queries to cal the job total n part total
for this invoice

the question is how to insert the sum of these 2 amounts to the invoice
table at the coloumn "total", under the same row of this invoice no created
earlier.

 

[John Vinson]

the question is how to insert the sum of these 2 amounts to the invoice
table at the coloumn "total", under the same row of this invoice no created
earlier.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]