Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Unanswered: Add a Calculated Value to a Table

    I have a form that I am inputing report information in. One of the fields I need to see how long between the date the report was started, and completed. I can get that to happen easy.

    I need to save that value in a field in the same table. I read that you can it using a macro using SetValue. I just read in the help file that SetValue doesn't work for calculated controls on forms. I am not sure how to do this now.

    Should I just bail on this idea, and have the calcs done in a query? Because I will need conditionals for the start and complete time. At the time being, there would be only 5 people using this, so I am not worried about huge loads. They would only use it a few times a week.
    Ryan
    My Blog

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Generally speaking, you shouldn't store a calculated value anyway, so I'd say yes, just do it on the fly.
    Paul

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    thanks. Is there a limit where I should start storing it?
    Ryan
    My Blog

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    A purist would say no, a pragmatist would say yes. Perhaps if it was a complicated calculation on a lot of records that slowed queries down unacceptably, I may do it.
    Paul

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    From what I've gathered, the general rule of thumb is to NOT store calculated data until the customer complains about load times; then try and find anywhere else that you can shave time (more efficient SQL's, etc.). And if you STILL can't meet their load times, THEN store calcs.

    But this is open to revision/brutal criticism as always
    Me.Geek = True

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Saving calculated values

    Quote Originally Posted by pbaldy
    Generally speaking, you shouldn't store a calculated value anyway, so I'd say yes, just do it on the fly.
    I disagree totally. Maybe in the days when harddrive space was an issue you wouldn't store the calculated values but who wants to argue when my query which has the calculated value takes 3 minutes to run (on 100,000 records) and yours takes 20 minutes to run because you have to calculate the values for every single record in the query?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Did you miss this Paul?

    Quote Originally Posted by pbaldy
    A purist would say no, a pragmatist would say yes. Perhaps if it was a complicated calculation on a lot of records that slowed queries down unacceptably, I may do it.
    If I had ANY query that took 3 minutes to run, I'd croak.
    Paul

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I forgot a few 0's and a 5 in the front (5 MILLION). I queried off of the credit bureau's database and the entire midwest energy conservation db. (I was talking about the WORST case scenario and trying to make a point!) Sorry, but I hate seeing posts which tell people never to store the calculated value. It's simply not something which should be told. It's an old philosophy and there are a few exceptions but it should not be related as the norm. My queries which took 3 minutes were actually querying off of 5 MILLION or so records (with many relational tables in them). And there was only 1 or 2 queries which took this long because they had about 15 relational tables in them with nested queries, were grouping, totalling and doing other things. If I had a simple query off of the 5 million records, then obviously seconds were the time (which I think was a feat in itself after a lot of indexing, tweaking and reorganizing table structure on that many records.)

    I didn't mean to bash you and did some typing before I thought about what I was typing. If you've ever worked with this many records with multiple, multiple relational tables, and grouping and totalling them in queries, then you'd rethink the "ANY queries which took 3 minutes" croaking. But if I had to calculate the values in those queries on every single record, it would actually take hours to run them (and that's what bugs me the most is then you have all these developers finding what I consider to be absurb work-arounds like running nightly procedures and telling users they have to wait until the next day to get their totals should they make any changes.)

    In my opinion, I have yet to hear good justification (with FEW exceptions - but not as the norm) for NOT storing calculated totals for something simple to program in. It takes 2-3 times or more longer running queries which have to calculate values - THAT WAS MY POINT!

    This is a tender spot for me as you can tell. It probably stems from having to fix so many #$!@ db's with pathetically slow running queries.
    Last edited by pkstormy; 07-16-07 at 19:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    For me, reluctence to store calculated values has nothing to do with disk space and everything to do with fear of update anomolies, especially with something like JET which does not support triggers.

    I am with Paul (B) on this but tend more towards the puritan than the pragmatist.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Did you actually read what I wrote, Paul? I said "Generally speaking", and I said I might do it under the conditions you described (and I said that before you posted). I didn't say "never". You seem to have an itchy trigger finger on this issue, and frankly you shot an innocent man.

    The OP's described calculation is a very simple one, and the described environment is not the environment in your example. Would you store something that simple in the OP's environment?

    To answer your question, I just ran an AR Aging report with a master table containing 800k records and a detail table containing 1.8 million, plus the customer table is linked. It ran in 20-30 seconds (in fairness, I do have SQL Server).
    Paul

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm going to go with:

    This is the access forum - therefore we have capacity limitations.
    Therefore we should calculate on the fly.

    And as Paul (B) aptly said --
    Quote Originally Posted by pbaldy
    If I had ANY query that took 3 minutes to run, I'd croak.
    George
    Home | Blog

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You're right Paul. I pulled the trigger to fast and for that I apology (the trigger finger is very itchy on this subject and it's difficult to hold back my opinon). (Boy did this post come at the wrong moment for me.)

    I stand by my thoughts on storing calculated. I don't buy the update anomolies thing pootle. You can experience that with other data. It just doesn't make sense not to store the calculated value (maybe I've designed too many accounting packages.) But if my users run a report on totals and they ask me why they are different than when they were a week ago, I want something solid I can look at in the database and not some calculation which depends on no hickups in the network (during the adding data time showing the total on the form or reporting totals time.) I also store any changed values.

    I've learned my lesson. When you have 5 currency fields and some complex calculation based on rounding and other factors, it's difficult not to justify storing the calculated value. If the rounding calculation is in error or the formula is bad which only affects certain records, it's easy to identify which records were affected without a lot of work. If the formula doesn't calculate right for some reason when they run their report, I want to see what it actually reported in the table. Plus, running that calculation in a report makes the report take "3" minutes when it could take "3" seconds. It goes hand in hand with having audit trails and makes it easier to run averages and other formalus on the total of the calculated value field verses having to run multiple queries or open multiple recordsets.

    "To answer your question, I just ran an AR Aging report with a master table containing 800k records and a detail table containing 1.8 million, plus the customer table is linked. It ran in 20-30 seconds (in fairness, I do have SQL Server).".....25 seconds to report on 1.8 means XX seconds on 5 million records? With 2? relational tables (each one adds up), and how many nested queries and what kind of formula? Any mult-field joins? How fast is the computer? How much memory? What is the network speed? Are the networks daisy chained? Are there other transactions happening against the table? Is citrix involved? Sorry, but these are all factors.

    I still think the simple calculated total the OP has described should be stored. True he has the dates stored. At some point he's going to want averages or other totals on that field. It may not seem like a big deal, trivial here, and not comparable to my example, but I would definately store it, if only to help confirm the dates were stored correctly.


    I personally think it's an old philosphy based on old conceptions which should be changed from "never store the calculated value" or "Generally you don't store the calculated value" to "Generally it's a good idea to store the calculated value." It's already gone from "never store the calculated value" to "Generally you don't store the calculated value" and I think it's a matter of time before people realize that "Generally it's a good idea to store the calculated value." (if we can get the concept into the head of the new developers (or old set-in-their-way developers) which state that you "never store the calculated value." - who teaches this stuff?)
    Last edited by pkstormy; 07-16-07 at 22:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pkstormy
    I stand by my thoughts on storing calculated. I don't buy the update anomolies thing pootle. You can experience that with other data.
    The entire basis of normalisation is to eliminate\ reduce the likelihood of update anomolies. As soon as you have transitive dependencies like this you run the risk of losing data integrity. I'll buy the performance claims but I'll never buy the idea that a calculated value is intrinsically more robust than calculations on the fly. Audit trails etc are all something of a red herring. I implement audit trails completely unrelated to calculated fields.

    Long and short of it - calculated values are a tradeoff between performance and integrity. I'll take integrity over performance any day of the week and would consider all sorts of things, including upgrading the engine, before I'd seriously consider storing calculated values. I understand that accounting packages are something of an exception here and perhaps our views are rather polarised as I've never had to build one of any real substance whereas you appear to have built a large number. I can swallow the idea of calculating a figure that will almost always remain absolutely fixed over time (e.g. an invoice total) but IMHO it is dangerous to over generalise this to other more volatile calculations.

    Anyway - I think we both knew each other's positions on this one before this thread. I guess we'll get to cover it all again next time someone asks the question
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by pootle flump
    Anyway - I think we both knew each other's positions on this one before this thread. I guess we'll get to cover it all again next time someone asks the question
    Or we could just have a battle royale and settle it right now
    Me.Geek = True

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by nckdryr
    Or we could just have a battle royale and settle it right now
    I'm weak and puny and wouldn't last five seconds
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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