I forgot how to add.....
I have a table "Estimate" that has field of "Cost", "Amountpaid1", "Amountpaid2" and "AmountDue".
How can have my table field "AmountDue" updated automaticly? (Sum-AmountDue1-Amountdue2)?
Or should it be on the form?
I forgot how...




Thanks
IB

Hi IB,

You can't (sensibly). Never include calculated values in tables (with the exception archives of some sort). They belong in queries. The following query will give you what you want:

SELECT Cost, AmountPaid1, AmountPaid2, [Cost]-([AmountPaid1]+[AmountPaid2]) AS AmountDue
FROM Table1;

As an aside, a better table schema would place your two AmountPaid fields in a seperate table on the many-side of a 1:M relationship with your table that contains cost information. Including the AmountPaid fields on your primary cost table creates what are known in normalization nomenclature as "repeating groups". They breed nasty insertion, deletion, and updating anomolies as your db design increases in complexity. (What happens if someone has to make 3 payments?). Tables that contain repeating groups are not even in 1NF (1st Normal Form), and a well designed relational db should achieve at least 3NF. I know you're probably thinking, "What the #(*$&! is he talking about." ;) But it all starts with a solid table schema.

HTH,

Bob

I concur with the Bobenator.

-Never store calculated fields in tables.

-Always provide for n number of instances (in this case payments. What if a customer was put on a payment plan for a year?)

-The query calc is good for this structure, but if you do normalize your tables you will need a different query, of course.



mmcdonal