Calculations on Forms !!!

ExpandCollapse

Guest

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 ???

"zizo batal" wrote:
> 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

ExpandCollapse

Guest

Thanks Tom . . .

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

Tom Wickerath wrote:
> 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" wrote:
>
>> 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

ExpandCollapse

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:

"zizo batal" wrote:
> Thanks Tom . . .
>
> However, I did the calculations using queries and then used Make-Table
> query to store the results in a table
>
>
>
> Tom Wickerath wrote:
> > 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" wrote:
> >
> >> 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

ExpandCollapse

Guest

On Wed, 24 May 2006 22:21:07 GMT, zizo batal <zizobatal@hotmail.com>
wrote:
>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.

About Us

Our community has been around for many years and pride ourselves on offering unbiased, critical discussion among people of all different backgrounds. We are working every day to make sure our community is one of the best.

Like us on Facebook

Support SPN

The management works very hard to make sure the community is running the best software, best designs, and all the other bells and whistles. We'd really appreciate your support!