Total Query

[aymanmb]

is there a way to calculate/store total cost value of qty x unitcost in total cost field of same table. if it requires a query, would appreciate guding me how to write it.

 

[Brianwarnock]

You should not store a calculated field in the table , this is against the rules of normalisation which exist to prevent data redundancy, you can calculate the values when needed in a report or query, and also to avoid errors as you would need to go to considerable lengths to ensure that any change to the base fields was reflected in the calculted field.

Brian

 

[Anthony George]

Re Total Query

Hi

Try a query like this:

Use a Colon : after the field heading and use square brackets.

Regards

Tony

 

[netgk5815]

Dealing with sales presents special cases

Orders and Invoicing require, for historical records purposes, that the price at the time of the sale and any applied discounts, etc. be saved. For performance reasons during report retrieval, it may be prudent to also store the calculated extended total costs for line items on the order/invoice so as to eliminate the calculation overhead from the retrieval process. This is especially so these days with storage being much cheaper than it was even 5 years ago.

You should not store a calculated field in the table , this is against the rules of normalisation which exist to prevent data redundancy, you can calculate the values when needed in a report or query, and also to avoid errors as you would need to go to considerable lengths to ensure that any change to the base fields was reflected in the calculted field.

Brian

 

[Brianwarnock]

Database design is like bidding in bridge, only break/bend the rules when you fully understand them and the potential consequences of doing same.

Brian