Calculations on Forms !!!

[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

 

[Guest]

Hi Zee,

You should not attempt to store the results of calculations; doing so
violates database normalization rules. In general, it is better to calculate
the results, when needed, on-the-fly.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[zizo batal]

Thanks Tom . . .

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

 

[zizo batal]

Thanks Tom . . .

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

 

[Guest]

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
__________________________________________

 

[Guest]

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
__________________________________________

 

[John Vinson]

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

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]

 

[John Vinson]

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

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]