Calculated fields

[Tbaby]

How do I create a field that consists of two other multiplied fields. Don't want to use a query. Pls

 

[PNGBill]

If you mean have three fields in a table instead of two ? eg QtySold, UnitPrice and ItemSales being the third which is a calculated Field.

This is widely regarded as Not Good Practice.

Access 2010 does allow such a field in a table. What version are you using ?

 

[Tbaby]

Thanx pngbill I'm using access 2007, I have fields qty sold, unit price, total price. I want my total price field to comprise of unit price * qty sold. If I must use a query how do I put the calculated field into my table. Pls

 

[PNGBill]

Maybe now your database is small and easy to manage but as time goes by, this may well change.

Good habits are best started asap.

Tables store Raw Data and Queries do most of the work.

Most of your forms will be or could be bound to Queries rather then Tables.

Queries can supply Up To Date real time data and results where as Tables can easily be out of date. The Raw Data will be correct, but may not be of use to you without a query manipulating same first.

In Sales, it is best to use a query to return the Invoice Total rather then store this in a Table. This avoids Redundant Data and means your result should always be correct.

Store the ProductID (sku), SalesQty and SalePrice but se a query to return the Invoice Details should this be required.

Don't store the calculated field in a table.
Reduce your Table by one field.

 

[Tbaby]

Auto update fields

So I have a field in a table named quantity on hand which I want automatically updated after every transaction. I'm using access 2007. How do I? Pls need some help!!!!! Thanx

 

[rodmc]

Id dont get this calculation field in a table, and why MS decided it was a good idea to allow it in tables, I dont see any advantage to it at all

 

[RainLover]

TBaby

What do you want. The Product of two Fields or the Quantity on Hand.

These are both very different Calculations.

Quantity on hand can require the storing of calculated values but the product of two numbers most likely does not.

Please explain what your goal is.

 

[Tbaby]

Well I was trying to add the product of two fields in another field. I decided to create a query and then calculate the fields in there. But now I want an update of quantity on hand each time a quantity is ordered.

 

[RainLover]

Calculating in a Query is correct.

Current levels is a bit more difficult.

Look here.

http://allenbrowne.com/AppInventory.html

 

[Tbaby]

Thanx rain, d link was very helpful ��

 

[RainLover]

There are many Variations with regard to Stock on Hand.

Recording the bottles of Milk for sale is a lot different to a Bank calculating each Customer's balance.

Think about what you need for your situation and write a solution that best fits.

 

[gemma-the-husky]

the reason you do not want to store the result of a calculation, is that you then need to ensure that everywhere you do something within your app, that affects the calculated figure, you have to recalculate that figure. it is so easy to miss something, and then you can get a situation where the calculated figure is unreliable

that is why it is better to calculate the figure every time.

there are exceptions of course, but hence the general rule

 

[RainLover]

Dave is correct when he says it is a General Rule. It can be argued that storing Calculated fields is anti Normalisation.

However there are more exceptions than most poeple realise. Even the link to Allan Brown's site shows the storing of calculated values.

Financials is one area that you must store a calculated value. You can't issue a cheque for $X.00 then change the value. Tax man does not like this sort of thing.

 

[The_Doc_Man]

Just to clarify, storing a computed number makes sense any time the data needed for the calculation is not always available. This almost NEVER happens when the sources of the computation are in the same table as the result of the computation. I cannot recall ever seeing such a case and would have to think hard about how to even contrive such a situation.

 

[Pat Hartman]

Id dont get this calculation field in a table, and why MS decided it was a good idea to allow it in tables, I dont see any advantage to it at all

Technically, the value isn't stored in the table. It just looks like it because Access calculates the value when it opens the table in datasheet view. If you create one, you'll notice that Access only allows columns from the table that "stores" the calculated value. So, essentially, Access is creating a query for you.

My advice is to never use a Jet or ACE "feature" that is not supported by SQL Server.

 

[RainLover]

One thing that is not discussed in threads like this (Storing Calculated Values) is Security.

In either situation, Stored or Calculated, one must address the users ability to change data.

The changing of underling values can cause the correct update of results. It can also incorrectly change the results. This is especially an important consideration where the original Result has beed used externally. Issuing a Statement to a Customer, Reports to Management or the processing of a financial transaction.

Where an alteration is required it should be done by a Reversal Type Transaction, not by simply changing values.

The locking of Data in MS Access, both Front End and Back End, should be gived the attention that it deserves.