Automatic calculation of a value based on the value of two other fields

[The Thing]

One of the tables in my Access 2000 database has two fields in it called Units Purchased and Price per Unit. How can I have access fill in a third field (Total Cost) in the same table automatically with a currency value that is calculated on the values of the aforementioned fields as in Total Cost = Units Purchased * Price Per Unit?

Thanks in advance for any help.

 

[dynamictiger]

It is not normal in Access to store a calculated value.

Rather these are calculated on the fly, in queries, forms and reports or what have you.

If you require the storage for tax purposes or some such, then why not add a field to the input form and store the result from there?

 

[The Thing]

With regards to my first post on storing calculated values in a table as opposed to generating the values on the fly for queries and reports etc, how much consideration should be given to this when designing a database. I ask this as I am using a commercial application - [Visual Basic front end and Access '97 database] - from Avery Berkel at work and I can see at least one field that is being stored in a table which is a calculated value.

Maybe this should be posted in the Forms section of the message board, but what is the code required to implement dynamictiger's suggestion?

Thank you for your help on this.

 

[Mile-O]

It took you one year to reply to this?

With regards to my first post on storing calculated values in a table as opposed to generating the values on the fly for queries and reports etc, how much consideration should be given to this when designing a database.

The word you need to look up for reference is normalisation (especially Third Normal Form [3NF]).

Having a calculated value stored in a table creates a non-key dependency. This means that the value of the field depends on the value of details in other fields (i.e. having three fields Price, Quantity, and Total). If you have the total calculated and stored in the table then problems begin to arise when you realise that it the quanity was two and not three. Changing the quantity means you'll have to manually change the Total field - or, as is more likely, forget to change it. Then, when it comes to summing the field there are going to be details that are - how shall we say? - off.

Using a query/report/form to calculate the total ensures that the data is always correct and saves a lot of space/memory that would otherwise be consumed by the unnecessary field.

So, do a search on normalisation and Normal Form on both this forum and Google. Both are a treasure trove of more detailed information.