Calculations on Forms !!!

  • Thread starter Thread starter zizo batal
  • Start date Start date
Z

zizo batal

I created simple calculations on a form that works just fine. However, I
can't get it to store the values of those calculations in a specified
field in a table!!! How can I get the form to store these calculations
to a table ???

Your help is really appreciated
Thanks
ZEE
 
Thanks Tom . . .

However, I did the calculations using queries and then used Make-Table
query to store the results in a table
 
Thanks Tom . . .

However, I did the calculations using queries and then used Make-Table
query to store the results in a table
 
Sure, it's possible to store the results of calculations. One method is using
a make-table query, as you discovered. There are other methods as well. The
point that I tried to make is that you should *not* be doing this. It
violates the principles of good database design (called normalization rules).
If you insist on going down this road, then suit yourself. But you'll only
have yourself to blame later on, when you have problems with inconsistent
data. Consider the following quote by database design expert Michael
Hernandez:

<Begin Quote>
"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."
<End Quote>

This is shown on page 23 of this document, which you can download for
yourself:

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Sure, it's possible to store the results of calculations. One method is using
a make-table query, as you discovered. There are other methods as well. The
point that I tried to make is that you should *not* be doing this. It
violates the principles of good database design (called normalization rules).
If you insist on going down this road, then suit yourself. But you'll only
have yourself to blame later on, when you have problems with inconsistent
data. Consider the following quote by database design expert Michael
Hernandez:

<Begin Quote>
"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."
<End Quote>

This is shown on page 23 of this document, which you can download for
yourself:

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
However, I did the calculations using queries and then used Make-Table
query to store the results in a table
Click to expand...

I agree with Tom. Don't!

Here's my standard spiel on the subject:

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]
 
However, I did the calculations using queries and then used Make-Table
query to store the results in a table
Click to expand...

I agree with Tom. Don't!

Here's my standard spiel on the subject:

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]
 
Back
Top