Results 1 to 5 of 5
  1. #1
    mlozano is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Location
    Madrid, Spain
    Posts
    11

    Storing calculated fields from form in yable

    I know calculated form control values should not be stored in tables. However, there are some situations where I cannot find an alternative

    1.- The default shipping address for an order is not valid and I want to save an "one off" alternative shipping address
    2.- Currency exchange rates vary in time. If I take the exchange rate into a query and use it for quotation, all my records' exchange rates will be updated.

    These are just a couple of pretty frequent situations where calculated form fields would need to be stored - unless you guys can help me find an alternative!



    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    I wouldn't disagree with either of those. Product price is another common exception.

    My wife and I enjoyed a nice visit to Madrid and Marbella last year. Only downside was people making fun of our lousy Spanish!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    mlozano is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Location
    Madrid, Spain
    Posts
    11
    Well your lousy Spanish was probably much better than most spaniards' non-existing english! I hope you enjoyed your stay and consider returning despite all the nonsense the media is pouring out on our country...

    On topic, I was posting this just to get some reassurance, as all I read about is how storing calculated values does against all rules and means certain trouble ahead...

    Cheers

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    Most people we talked to were very nice, and at hotels/restaurants their English was usually better than our Spanish. My wife and I are both in the same situation. We were fairly conversant in Spanish many years ago in school, but lost much of it in the intervening years.

    The caution against storing calculated values is valid, but it could be said that the items you mentioned are attributes of the sale, not of the customer/product, and not calculations either (the product of quantity times price would be a calculated value).

    The purely normalized approach would probably be to have a table with multiple addresses for the customer, and storing the key value of the appropriate address with the sale. Same with prices or exchange rates, or you'd store a price with a date range and look up the appropriate price/rate based on the sale date. From a practical standpoint, it makes more sense to simply store the value with the sale.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,018
    As Paul pointed out, you're really not speaking of storing the Calculated Field, but rather storing a Component of the Calculation, in multiple places. And I agree with Paul that going with the 'purely normalized approach,' here, is really overkill, and storing the component in multiple places really makes more sense than having to, each time you move to a Record
    • Look at the date of a quotation
    • Access the Table of exchange rates
    • Find the given currency
    • Compare dates for that currency until you find the appropriate exchange rate
    • Return to your Form and run the Calculation using that exchange rate

      Linq ;0)>

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculated Fields in Entry Form
    By Rhubie in forum Forms
    Replies: 1
    Last Post: 09-05-2012, 01:17 PM
  2. Replies: 0
    Last Post: 02-24-2012, 11:16 AM
  3. Storing Calculated Value (special senerio)
    By robsworld78 in forum Forms
    Replies: 5
    Last Post: 07-11-2011, 10:17 PM
  4. Replies: 1
    Last Post: 05-04-2011, 03:51 PM
  5. Calculated fields in form
    By speckytwat in forum Access
    Replies: 15
    Last Post: 04-27-2011, 05:01 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums