storing calculated values in a table

[msoccer29]

Hi All,

Any help or direction is much appreciated.

I've created a simple table in Access, including Name and Cost. I want to add a third field called Tax which will populate itself based on the value in the Cost record * .13.

I understand it would make more sense for this to be in a query, but I'm wondering if there is anyway to make it so then when a new record is added and say, 100 is typed into the cost field, 13 automatically shows up in the tax column based on the equation.

I guess this problem is something Excel would ovbiously handle perfectly, but it is necessary to use Access. If this cannot happen, is there anyway I can run a query of sorts and then add the results as a new column to the table.

The ultimate goal here is having the tax column as a part of the Table without manually calculating the values.

Thanks Anyone for their time...

[MikeTheBike]

Hi All,

Any help or direction is much appreciated.

I've created a simple table in Access, including Name and Cost. I want to add a third field called Tax which will populate itself based on the value in the Cost record * .13.

I understand it would make more sense for this to be in a query, but I'm wondering if there is anyway to make it so then when a new record is added and say, 100 is typed into the cost field, 13 automatically shows up in the tax column based on the equation.

I guess this problem is something Excel would ovbiously handle perfectly, but it is necessary to use Access. If this cannot happen, is there anyway I can run a query of sorts and then add the results as a new column to the table.

The ultimate goal here is having the tax column as a part of the Table without manually calculating the values.

Thanks Anyone for their time...

Hi

The general rule is don't store anything that can be calculated!

As you rightly pointed out Excel will handle this perfectly, even to the point of recalculating the tax if the value is changed from 100. This would not happen in Access if you stored the calulation as 13.

It realy depends on what happens over time, eg. a calculation for an invoice which is fixed in time and will not depend on the value (100) or the tax rate (0.13) in the future, then do store the calculation (this will have to be done on a form even), if not then use a query.

That is my, very brief, explanation, but if anyone else would like to chip in ....

MTB

ps should the tax rate(s) be stored in a table ??

[MMcCarthy]

As Mike said the storing of calculated values is not advised. However, if you need to do this and the tax rate is not likely to change then you can populate it using the data entry form. Set the default value of the control to

=[Cost] * 0.13

However, if the tax rate is subject to change then you will have to look at storing the tax rate somewhere and using a different approach.

You will also have to look at whether a change in the tax rate would historically change the values in the table as this is almost impossible with a calculated field which is why its not normally stored.