I'm still totally against the stigma that storing calculated fields is always a "Don't do it" type of philosophy. I've designed several software packages where it was MUCH easier and a BLESSING for auditing purposes by storing the calculated field. I guess once someone gets audited on a financial package (and several other situations) might instigate a little more emphasis that storing calculated fields is not really a "bad" thing (versus portraying it as a one-time certain situation only - there are actually several situations.) For myself, I'd rather store a calculated field whereby I can easily design other queries to do averages and other calculations (without having to create temp tables or nested queries) or don't want to wait 20 minutes for a 3 million record report to do each calculation versus trying to stick to some "normalization" rule which says I shouldn't.
Also keep in mind that you can't sum a set of calculated fields on a continous form unless you want to write some code and/or additional queries.
As for myself, I prefer to see the calculated value IN THE TABLE versus ONLY on some paperwork in someone's desk when it comes time to qc the totals and reports and get audited. If the equation ever changes, you then have to mark which records used the one calculation versus the other (I've even put the equation itself in a field in some of the financial packages I created.)
That's not to say that I always store the calculated value but I certainly don't avoid doing it because some normalization rule which says I should NEVER do it. I've seen way too many developers design pages of work-around coding just to get around storing the calculated value and it's sad to see newbies struggle because someone decided that you should never do it. And yes, you do have to take precautions when doing it but better to learn how to deal with those now versus when you're on a time-crunch to get the project done.
As for the original post, I personally would store the calculated value. But I'm probably in the minority on this. Guess I've been audited too many times and designed too many financial packages. Almost all of the auditors I've worked with want to see the calculated field in the table.
If you're not in a time-crunch on this project, I think you should maybe give it a try just to see what precautions you need to take when storing the calculated field. The worse-case situation could be that it doesn't store/update the calculated value correctly when it should which could cause you a lot of misery. You have to make sure when you store the calculated value that it gets updated correctly when it should. If it's a straight-forward X+Y=Z, it may not be worth it unless you wanted to do further calculations with the sum of all the Z's.
Last edited by pkstormy; 02-26-09 at 03:23.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)