Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Calculations in tables
- Thread starter kiwismom
- Start date
Actually, you can't _and_ you shouldn't. I'll explain...
A table stores data. There's no way to make a field in a table calculate anything. That's the "You can't" part.
The "You shouldn't" part is a little more involved, but quite important. One of the first principals of designing databases is that, except in rare cases, you should not store calculated values. If you store three test scores, you should not store the average. You can always calculate the average for display. And if someone retakes a test (more generally, if one of the factors of the calculated value changes), you have to make sure to recalculate, or else you'll be storing conflicting data.
You can, and should, create queries that do these calculations for you every time you pull up the data. Look into the help files for select queries and calculated values. You'll find tons of information there. You can also do calculations in individual controls on a form or report. This is also a powerful tool.
For an excellent discussion of the basic principles of database design, take a look at the Developers' section of my web site. I've got an article there by Paul Litwin, one of the gurus of Access development, and he explains all of this stuff quite well.
Hope this helps.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
A table stores data. There's no way to make a field in a table calculate anything. That's the "You can't" part.
The "You shouldn't" part is a little more involved, but quite important. One of the first principals of designing databases is that, except in rare cases, you should not store calculated values. If you store three test scores, you should not store the average. You can always calculate the average for display. And if someone retakes a test (more generally, if one of the factors of the calculated value changes), you have to make sure to recalculate, or else you'll be storing conflicting data.
You can, and should, create queries that do these calculations for you every time you pull up the data. Look into the help files for select queries and calculated values. You'll find tons of information there. You can also do calculations in individual controls on a form or report. This is also a powerful tool.
For an excellent discussion of the basic principles of database design, take a look at the Developers' section of my web site. I've got an article there by Paul Litwin, one of the gurus of Access development, and he explains all of this stuff quite well.
Hope this helps.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developer's section of the site for some helpful fundamentals.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question