Is it possible to append the result of a calculated text box on a form to a table

[dombrost]

Hi all. I am total newbie to Access so any help is greatly appreciated. I am creating a simple database in Access 2003 that captures details of an operation event.

I created a form for the user to enter the details of the operation event. I also created a calculated text box on the form that has an expression that takes the values the user entered into the form and spits out a value.

Is their a way to append(i'm not sure if that's the right terminology) the value from the expression on the form to a table in the database?

Thanks in advance.

[orangeCat]

Hi all. I am total newbie to Access so any help is greatly appreciated. I am creating a simple database in Access 2003 that captures details of an operation event.

I created a form for the user to enter the details of the operation event. I also created a calculated text box on the form that has an expression that takes the values the user entered into the form and spits out a value.

Is their a way to append(i'm not sure if that's the right terminology) the value from the expression on the form to a table in the database?

Thanks in advance.

I'm sure it's possible, but many here will question you as to why you want to store a calculated value. Perhaps you could describe what you're dealing with and what exactly you want to store.

[dombrost]

I would like to be able to pull a report from the table that has the field values and calculated value from the form. I tried creating a query that has the same expression from the calculated text box on the form. When I went to use the report wizard it said I could only use fields from either a table or query and not both. The answer might be obious- should I just create a query that contains the expression and the field I want from my table?

Thanks for your time.

[missinglinq]

You don't need a separate query to do this. You simply place an unbound textbox on your report and then do the same calculation you did on the form.

As orangeCat suggested, it is seldom necessary or advisable to store calculated values. There are some reasons for doing so, but this is certainly not one of them.

Welcome to Bytes!

Linq ;0)>

[dombrost]

That sounds good. Thank you all for your help.

[trixxnixon]

so what would be a good reason for storing a calculated value? and why would one argue against it?

[nico5038]

Storing calculated values is against normalization rules (e.g. check out http://en.wikipedia.org/wiki/Database_normalization).
The only reason for storing calculated values is the fact that the response time for processing the source data is too long and the calculated values are used multiple times. This will however hold the risk that the calculated values are "out of sync" with the actual data.....

Nic;o)

[Stewart Ross]

In most circumstances, calculated values do not need to be stored as to do so would introduce unnecessary redundancy in the stored data. For example, in a purchase order database it would be redundant to store the subtotal of the order line quantity and unit cost, as this can be calculated as necessary from its components.

In some cases additional information is needed (for example, the actual tax rate for an item prevailing at the time of sale, or the rate of any discount applied) which would have to be stored with the other data for that row in the table. This would still not require that the overall subtotal inclusive of tax or discount, say, be stored. There is simply no need as long as the components of the calculation are present and correct.

As Nico said, storing calculated values breaches normalisation rules - the calculated data represents a functional and possibly transitive relationship between two or more fields within the row concerned. The calculated field depends on the nature of the function and any transitive relationship involved; it is not determined entirely by the unique key of the row.

That is, the calculated value is a function of its components f(a, b, ..., n) where a, b, ..., n are the fields which feed into the function concerned. A simple function may involve just one variable (e.g. multiplication by a constant), but in the purchase order example there up to four variables (quantity, unit cost, tax rate, discount rate).

Whilst the value of the components of such a calculation are related to the unique key (as they represent real-life attributes of, say, the purchase order line concerned), the calculated field is dependent not on the key but on the values of the components. It therefore breaks normalisation rules, which require that the value stored be wholly dependent on the unique key for that row (i.e. that the value represents some real-life attribute that we must store in order to have sufficient information to model the item concerned).

Functional and transitive relationships between fields are normalised out as part of the process of database design to reach 3NF or higher forms.

-Stewart

[NeoPa]

Further reference on this point may be found at Normalisation and Table structures.

Written by one of our own members and as well explained as I've found - especially for those new to the subject.

[NeoPa]

A new question was posted in here and has now been split off into Normalisation Related.