Access 2000 Question- How do I auto update a field?

[Guest]

Access 2000 Question - How do I auto update a field in a table with the sum
of two other fields? Is there a way to update a field, in a table, with lets
say the sum of two other numbered fields in the same table, Example: I have 3
fields named "sum1", "sum2" and "total" in a single table. I entered a value
of 100 in both "sum1" and "sum2". Is there a way to make "total" update
itself with the total of 200 in the "total field. I know how to do it in a
form, but I want to update the table with the value. Can it be done? I hope
this makes sence

 

[Larry Daugherty]

Access tables may look like Excel spreadsheets but they are very
different things in two very different products.

The answer to your question is "You can't". Tables are containers for
data they are not spreadsheets. To do what you want you could use a
Form and / or a Query. However, doing a calculation on two elements
in a record and then storing the result in the record is a violation
of Relational rules. The accepted practice for what you're trying to
accomplish is to calculate and display the result whenever it's
required; i.e. on a Form or in a Report. Really.

HTH

 

[Guest]

Storing the results of a calculation in a table is generally not a good idea.
Doing so violates database normalization guidelines. You should spend some
time gaining an understanding of database design and normalization before
attempting to build something in Access (or any RDBMS software for that
matter). Here are some links to get you started. Don't underestimate the
importance of gaining a good understanding of database design. Brew a good
pot of tea or coffee and enjoy reading!

http://www.seattleaccess.org/
(See the last download titled "Understanding Normalization"
in the Meeting Downloads page)

<Begin Quote (from page 23 of document)>
"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>

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

If you really must store the results of a calculation, then you most
certainly should implement JET Check constraints, so that the independent
values (sum1 and sum2) must add up to the value stored in the Total field.
But, it is a rare situation that you should really need to do this, and JET
Check constraints is considered an advanced topic. By the way, if you want to
see a glaring example of the type of errors that can occur, study the image
carefully at this link:

http://office.microsoft.com/en-us/templates/TC010185481033.aspx?CategoryID=CT011366681033


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________