1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Store formula results in Access 2003

Discussion in 'Business Applications' started by hqnet, Apr 18, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. hqnet

    hqnet Thread Starter

    Joined:
    Nov 14, 2009
    Messages:
    102
    Hi,

    based on this...
    ...I will be storing values calculated on a few formulas in the DB, I wonder if thers is some sort of recommended way to do it?

    So far my approach would be like this:

    Ctl A - input field using table field as 'data source'
    Ctl B - input field using table field as 'data source'
    Ctl C - unbound field with formula to calculate and show on screen the Resulting Value.
    Ctl D - "print" field where Results are 'echoed' so they get stored in the bound table field (using table field as 'data source')

    I am using C and D because the source of C is a formula instead of a table field and therefore it can't be stored.
    Maybe there is a way to avoid this and I should do that instead?

    TIA.
    HQ.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Although storing calculated values is not recommended as it wastes disc space, if you have plenty of space, it doesn't really matter. As you indicated in your previous post sometimes it is easier processing wise to store the value rather than calculate them each time you need them.
    So what are the calculations that you are using?
    You can store the calculations in a Table field, but you use VBA code to do the calculation, which is quite easy to do.
     
  3. hqnet

    hqnet Thread Starter

    Joined:
    Nov 14, 2009
    Messages:
    102
    Hi, sorry for my delay

    I dont have the formulas with me right now, but they are something close to this, where A and B are input data, 3.95 and 0.15 are constant factors.

    (3.95 - (0.15*A))*(B)

    So far these are set as the source of the text controls in subforms (I was mostly working on getting maths working rather than storing the results).

    There are around 10 of these calculations, but it should be noted that while having these results on screen is important, the ulterior point is using these results to plot some charts in an additional form. I will be storing the values because I need to lookup the results for those charts.

    So far the results are not being stored yet, and are only shown on screen.

    Best regards :)
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Those sorts of calculations can be done in a Query and Charted from that if you want.
     
  5. hqnet

    hqnet Thread Starter

    Joined:
    Nov 14, 2009
    Messages:
    102
    Hi,
    to be honest, Queries go out of my league... I've built a couple in this project but I was just lucky :p

    I wouldn't know how to build a Query with calculations, maybe if I see an example I could expand it to my needs, but I don't want to put you trough so much trouble, you helped me so much already that I am embarrassed to keep asking for help :eek:
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Don't worry about asking for more help, it is what I do on here. I have worked with some posters for up to 3 years off forum.
    Here is a simple database with a Query showing simple calculations and text Concatenation, with text you can also use left, mid & right to cut up the field text in to sections. With Dates you can use Format() or Year(), Month() etc to cut them up in to their component parts. You can also use IIF() to make decisions as well.
    The Query, along with VBA are the powerhouse of the Database.
    Perhaps I will expand the queryto include those others I am less busy.
     

    Attached Files:

  7. hqnet

    hqnet Thread Starter

    Joined:
    Nov 14, 2009
    Messages:
    102
    Well, you do live up to your signature, Mate :)... no doubt about that !

    Thanks a lot, I will be looking into it in the next days.

    Best regards!!

    (PS: I hope my wording was right, English is not my native language...)
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Your English is great, good luck with the query.
     
  9. hqnet

    hqnet Thread Starter

    Joined:
    Nov 14, 2009
    Messages:
    102
    well, who would know? .. With the guide of you sample I was able to build the Queries I needed and use them to feed the charts! :D :D :D

    Thanks a Ton!!


    I wonder how I should proceed with the Controls I had made initially... I mean, now I have on one hand the Queries and the charts taking data from them, and on the other hand the Controls in the sub-forms with the same formulas printing the results on screen.
    ┬┐should I use the Queries to feed the Controls instead?

    Cheers!
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If the Subform is working OK you might just as well leave it as it is.
    But if you have the time and inclination you could use the query data instead, providing it gives you what you need. It would be a good learning experience to try it with queries ad make it work.
    I have used both methods in the past.
     
  11. hqnet

    hqnet Thread Starter

    Joined:
    Nov 14, 2009
    Messages:
    102
    I have the inclination, not so sure about the time though ;)

    It's good to know keeping it this way would be acceptable, but I will try to make the time to use the queries. I could use the learning and streamlining code is always healthier in the long run :)

    Best regards OBP!

    *marking as solved*
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    By the way, well done in getting it working in your real world database.(y)
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/917690

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice