calculated text box returning value to table

D

Dave Smith

hi, i have a text box which has a calculation in the control source, it
works fine, but i want the "answer" to be saved in the table. i have tried
putting the caclation in the "default value" & putting the name of the field
in the control source, and i have tried leaving the first text box & making
a new one with its default value pointing to the first with the name of the
field in the control source. i'm sure eiter of these 2 should have worked &
i'm not sure what i'm doing wrong.

Cheers for your help

Dave Smith
 
T

Tom Wickerath

Hi Dave,

You should not attempt to store the results of any calculation in database
tables. Doing so violates 2nd and 3rd normal forms of database design.
Consider what database design expert and published author Michael Hernandez
has to say on this subject:

"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."

Source: See page 23 of "Understanding Normalization", which you can download
at
http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")


Tom
___________________________________

:

hi, i have a text box which has a calculation in the control source, it
works fine, but i want the "answer" to be saved in the table. i have tried
putting the caclation in the "default value" & putting the name of the field
in the control source, and i have tried leaving the first text box & making
a new one with its default value pointing to the first with the name of the
field in the control source. i'm sure eiter of these 2 should have worked &
i'm not sure what i'm doing wrong.

Cheers for your help

Dave Smith
 
J

John Vinson

hi, i have a text box which has a calculation in the control source, it
works fine, but i want the "answer" to be saved in the table. i have tried
putting the caclation in the "default value" & putting the name of the field
in the control source, and i have tried leaving the first text box & making
a new one with its default value pointing to the first with the name of the
field in the control source. i'm sure eiter of these 2 should have worked &
i'm not sure what i'm doing wrong.
Click to expand...

What you're doing wrong is trying to store the calculated field in the
first place!

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you have some SPECIFIC reason why this field should be stored, post
back with an explanation; it can be done - but generally it
*shouldn't* be done.

John W. Vinson[MVP]
 
D

Dave Smith

thanks so much for the advice, if it makes any difference, i wanted to store
the value so i can chart the value, but i guess it's no big deal. i'm
learning all this stuff slowly & i didn't realise that it could pose a
problem of course. if you get time drop another line but i'll take your
advice for the time being...

Dave Smith
 
J

John Vinson

thanks so much for the advice, if it makes any difference, i wanted to store
the value so i can chart the value, but i guess it's no big deal. i'm
learning all this stuff slowly & i didn't realise that it could pose a
problem of course. if you get time drop another line but i'll take your
advice for the time being...
Click to expand...

Just don't assume that you must have the data in a Table to chart it.
You don't. It's possible - routine, in fact - to base a Chart on a
Query; and you can have a calculated expression in a query field.


John W. Vinson[MVP]
 
Top