in MS access, how do I return a calculated value to the table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have built my table and my form, I can build an expression on the form that
performs and displays the calculation I need, but I can not make it return
the calculated value to the table. there is a field for the result on the
table but it remains as 0. Please be detailed in the answer as I am new to
access
Thanks
 
You should not be doing that. One of the basic rules of database design is
that you do not carry any calculated values, you calculate them only for
displaying on a form or a report, or if they need to be used in another
calculation. It wastes space, takes extra time, and calculated values can
get out of sync and become incorrect.
 
I have built my table and my form, I can build an expression on the form that
performs and displays the calculation I need, but I can not make it return
the calculated value to the table. there is a field for the result on the
table but it remains as 0. Please be detailed in the answer as I am new to
access
Thanks
Click to expand...

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.

John W. Vinson[MVP]
 
John, I think I may be learning a trick from you. I have seen your reponse
to this question numerous times. It is always (so it seems) exactly the
same. So, where are you keeping your canned responses you cut and paste
into a reply? :)
 
John, I think I may be learning a trick from you. I have seen your reponse
to this question numerous times. It is always (so it seems) exactly the
same. So, where are you keeping your canned responses you cut and paste
into a reply? :)
Click to expand...

I use Agent as a newsreader (www.forteinc.com); there's a File...
Insert Text File option. Other volunteers use a variety of other
newsreaders or newsreader assistance tools - I know that there are
tools that allow a single-keystroke shortcut to such things, but I've
never really bothered to investigate such.

John W. Vinson[MVP]
 
Thanks for your input, My Database aint that big and it will be on a rather
huge gov't system so space isn't a problem. But thanks none the less
 
Size doesn't matter. :)
Its all about good database design and data integrity. If you store a
value such as a person's DOB and have another field that represents the
person's age, you'll have to have some way of recalculating the age
field to insure that its updated when the person has a birthday. It is a
*lot* of work to do that. Storing the key value (DOB) and then doing the
calculation when a form/report/query is viewed saves an *inordinate*
amount of time.
 
Thanks for your input, My Database aint that big and it will be on a rather
huge gov't system so space isn't a problem.
Click to expand...

Space is trivial.

Data integrity is important.

If you don't mind having INCORRECT DATA stored in your table, or
having two records containing different values for the "same" data,
with no way on Earth to tell which is correct, go right ahead.
Otherwise I'll stand by my suggestion to recalculate the data rather
than storing the calculated value.

John W. Vinson[MVP]
 
What if you needed to calculate a value and you wanted to store it in the
table becuase it is going to be grouped based on other criteria and each
group averaged.

Would you want to store it then or just create a query that did all of the
analysis?
 
Hi Aaron,

I agree with John, you should not store the calculated
value... put the equation in your query (or the underlying
SQL) for your report.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
Back
Top