Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2005
    Posts
    11

    Exclamation Unanswered: How to store calculations in a table

    I have created a form using a query and in the query I have some basic calculations for purposes of invoicing. But when I add the record, it does not record the results of the calculations in the table. Can anyone please suggest a way of overcoming this problem.

    Query:

    (Field) GST: [Total]*.1

    Query:

    (Field) Total plus GST: [Total] + [GST]


    These two calculations work fine, but they do not store in the underlying table. Can anyone suggest another method

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by platinumblak
    These two calculations work fine, but they do not store in the underlying table. Can anyone suggest another method
    Hi platinumblak and "Welcome to the Forum"

    What you are asking is something I've been advised to stay away from. Unless for historical reasons and fields are not going to change it has been said to just do the calculations in a Query/Form/Report. You will always have access to those results whenever you need them.
    Now, though I have never done what you are asking I just tried something that might get you what you want. I did the Query to get the results, THEN I turned it into a MakeTable Query to just hold the results ASIDE from the MainTable you are using. That way everytime you do your calculations, have it do an Append to the CalculationTable to store those values for later use. I'm pretty sure there is a more eloquent way to do what you are asking, but that's just something I came up with off the top of my head...being as I have never stored calculations in a Table.

    hope you enjoy the Forum
    have a nice one,
    BUD

  3. #3
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    One of the rules of relational databases states that tables can not store calculations. This is why Access does not permit it and why it does not allow derived data to be stored in a table.

    There should never be a case for storing the results of a calculation, or the calculation itself, in a table. If you wish to calculate something it should be done dynamically when needed, in a query or report. This ensures that you are always applying the calculation to an up-to-date data set. If you were to store the results of a calculation in a table, it would be "dead" data.

    If you find yourself needing to store the results of a calculation in a table, then I'd respectfully suggest that there is something amiss with the design of your database. If you wish to calculate invoice data for a report, for example, then base that report on a query.

    Hope this helps.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by andybriggs
    One of the rules of relational databases states that tables can not store calculations.

    That's not necessarily true. Occassionally performance becomes a serious issue and either a compiled view/stored procedure or stored values are worth looking at. That rarely happens and should be a last resort because it introduces bloat and the potential for inconsistancy.

    That said, I agree with the above posters.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    It may be sophistry but
    There should never be a case for storing the results of a calculation
    isn't neccesarily true.

    There are good reasons for sometimes storing derived columns, however they are few are far beteween. The usual reason being for performance, ie having the data in its derived format can be retrieved quicker (for example as an indexed field) or smarter use of the data (for example in a data warehouse). I have also seen it used when using Foreign Exchange calculations, where although the local currency value could be derived it was deemed appropriate to convert at the time of data capture to avoid having to retrieve the relevant rate based on the date and time of the transaction taking place.

    However I suspect that for the vast majority of MS Access applications Andy is right in saying
    There should never be a case for storing the results of a calculation.
    HTH

  6. #6
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Stored procedures, fine. But I'm afraid that one of Codd's Twelve Rules IS that tables must not store derived data. If performance is an issue, there are workarounds as you say, but they should never involved storing derived data in a talble
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  7. #7
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    There was just something else I wanted to say on this. We all occasionally come across real-world situations where the easiest solution is to store derived data in a table - it's not a perfect Universe - but as a general rule one should, I feel, adhere as closely as possible to the rules of relational databases in order to try and preserve some sort of formalism. However, storing such data in a table does, I feel, negate one of the main strengths of a relational database.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    A rule is rule and worthwhile following, unless of course it impedes progress to the final target. The Codd rules are a worthwhile general principle, but there are, and always should be exceptions to, or prove the rule.

    Being defensive I did say it was sophistry and also that it was rare to use derived columns. But I still believe that there are valid reasons for storing derived data. However being realistic the last time I needed to was some 4..5 years ago and a lot of development has gone on since that time.

    Andy
    Just read your follow up posting, agree entirely as a general rule dont' stroe derived columns, unless, of course you have to!

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    All posters here seem to agree very closely on NOT STORING derived data in a Table. I'm not sure if the original poster platinumblak is truly aware of this or not. If you are a beginning user of Relational Databases platinumblak, then do take into account all the info. that has been provided to you here. If you are advanced, then I would think you know the rules. What exactly, may we ask, is your reason for wanting to STORE this data in a table anyway? Just so that we can all get a better understanding of what is going on with your program. And do understand we all mean to help you and say all this in a helpful, positive and friendly manner. Sometimes it just helps to know more of what a program is to do and why in order to remedy a solution.

    have a nice one,
    BUD

  10. #10
    Join Date
    Jan 2005
    Posts
    11
    I am well aware of the rules but its essential that I store the calculated results in the table because I have an 'export data to excel' function, for purposes of graphing/charting. This is used to create reports and it has to be an excel file because thats the directive of who I am reporting to.

    So i need to find a way to store the calculated data, so that when I export the table, all the data is available.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hi
    In which case I'd be temtped to export a Query, rather than the base Table, to Excel.

    The advantage being that you can perform what ever analysis you wish as part of the query (and the generation of the derived column), and include other normalised columns if required.
    HTH
    Last edited by healdem; 01-08-05 at 10:29.

  12. #12
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Smile

    Quote Originally Posted by healdem
    Hi
    In which case I'd be temtped to export a Query, rather than the base Table, to Excel.

    The advantage being that you can perform what ever analysis you wish as part of the query (and the generation of the derived column), and include other normalised columns if required.
    HTH
    I totally agree with HTH....and use the Query. Matter of fact you can Import that Query from Excel if you want. If you need a hand in doing that there are some people on here who can guide you into importing data into Excel. That is what Excel prefers anyway So let us know if you need a hand still. I can walk you through a way to do it, might not be the quickest but it works. I used it in the past to keep Excel files updated automatically to graph weekly dataflow for presentations. Doing it at Query level is best and flexible as well and really the preferred method of extracting data to another source.

    have a nice one,
    BUD

  13. #13
    Join Date
    Jan 2005
    Posts
    11
    thanks a heap everyone, I am using the query export suggestions, its working fine so far

  14. #14
    Join Date
    Mar 2006
    Posts
    1

    storing calculations

    I need to store the value of calculated text box on a form as this data will never change and it has to be stored for tax return purposes how would i go about taking the value of that transaction and storing the text box value into the table where i have made provision for it with each transaction.

    Regards Mel

Posting Permissions

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