Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    29

    Unanswered: Storing calculated fields

    I'm having problems storing a Total field in a table. I know all the books say not to store a calculated field, but in some instances it needs to be done. If I have an Orders table, I need to know the OrderTotal of each OrderNumber. Each OrderNumber will have several calculations on the form. Obviously the CostPerItem is derived from the product of Quantity and UnitPrice. And the OrderTotal is derived from the sum of the CostPerItem. How can I save the value in the OrderTotal in my Orders table?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    To clarify, it doesn't need to be done; you want it to be done. In any case, there's a method here:

    Microsoft Access tips: Calculated Fields
    Paul

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    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)

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Must be a "hot button" issue for you, as I didn't say "Don't do it". I'm a pragmatist; there are situations where any "rule" can and should be broken. That said, I've worked with databases that did store calculated fields, and done it myself. Users will always find some magical way to get the calculated field out of sync with the base fields, so if you do it, make sure you watch out for that.
    Paul

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    occasionally there are times when rules should be set aside, for me that when you allow the souless zombies from accounting or auditing near a computer program. its also advantageous in my mind for invoices to have an exact replica of the invoice that was actually sent.

    but those are special cases, usually the need (or as PBaldy mor accurately defines the desire) to store calculated values is oftne a symptom of bad design

    or if there are performance issues, but performance issues are often also a symptom of bad design
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •