Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2004
    Posts
    48

    Unanswered: Microsoft Access Question

    Can the results of a calculating text box on a form be stored in a table? If so, how. Thanks.

  2. #2
    Join Date
    Jul 2006
    Posts
    108
    not quite understanding you, could you please elaborate a bit more...

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Sure, the value can be stored, just like any other data. Most people here, however, will tell you that storing a calculated value in a table is not the thing to do, as the calculation can be done each time the record is displayed. I'm one of the dew here who will tell you that there are times when it's appropriate. Exactly what is it you wish to store?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Aug 2004
    Posts
    48

    Reply to jwalker and missinglinq

    Thank you for your responses.
    I'm designing a form for a user in the company I work for. The user wants a field that will calculate a date 6 months from an existing date already on the form. The user doesn't want the field to be for display purposes and needs the data saved. How do I store the results in the table from the field on the form that is doing the calculation? Perhaps the question should be Can I create a field in the table that will do the calculation?

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'll agree with Missinglinq that there are times when this could be appropriate. IMHO, this is not one of them. It's quite easy to calculate this date whenever you're displaying data. Unless your user understands relational database design, they should outline the requirements and leave the "how" to you.

    The short answer to your question is no, you can't create a field in the table that does the calculation. That's would be a spreadsheet, not a db.
    Paul

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You can't add a field to a table that will calculate the date, but you need a field in the table to store the date, if you must. In the AfteUpdate event of your date field do something like:

    Code:
    Private Sub OriginalDate_AfterUpdate()
        
         NewDate.Value = DateAdd("m",6, OriginalDate.Value)
    
    End Sub
    Where OriginalDate is the field you enter the date into and NewDate is the field on the form holding the calculated date. The Control Source for NewDate has to be the field in your table that is to hold the calculated date.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Totally agreed with Paul (and I doubt this is one of the circumstances Missinglinq was alluding to). The user's requirement should be "I need to see a date six months after this" not "I want you to store a date six months after this". One pretty simple reason is - what happens if the original date changes? Basically this comes down a principle of normalisation (no mutual dependancy between non key columns) -
    http://r937.com/relational.html

    If the user is your boss you could suggest an upgrade to a server side RDBMS - SQL Server for example supports calculated columns like this.

    And finally - if you decide to save the data to a table - then you can do so just like you save any other data to a table.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2006
    Posts
    108
    elizabeth try this:
    if you have field1 on display and you want field2 to be +6mo of field1 then create a new column in your table and name it field2. set the default value of that coulmn to be dateadd("m", 6, [field1])

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It sounds to me like you might want the Default value to be 6 months from the specified date. In that case you would want to store that value.

    Once that date has been calculated will there ever be a need for the user to change it?

  10. #10
    Join Date
    Aug 2004
    Posts
    48

    Response to Missinglinq, jwalker, et al

    Thanks very much for your suggestions. So far I've tried the one suggested by Missinglinq and it worked so I'm a happy camper. Thanks again.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Just as a note regarding storing calculated values. If you've done any programming with accounting systems, storing calculated values are a necessity. I get a little tired of hearing from users that "you never store a calculated value". There are many times (besides accounting programs) where I've found this to be the best solution and will be more than happy to dispute and show the pros on doing this.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I agree, Paul. The old adage "never say never" comes to mind! Calculated dates, particularly, lend themselves to storage. I have several apps where the calculated value of a date field is based on the previous value of the same field; VisitDate becomes the next VisitDate. Obviously, you can't calculate that in the future! I don't do accounting type apps but I would think there are many times when calculated values need storing. Calcualting cost of an order, for instance; base cost, discount, tax and shipping. Total cost would be based on 4 factors, three of which (discount, tax and shipping) could easily change between the time of the order and a date in the future when the transaction is viewed. I'm sure you could set up a complicated scheme where the date of invoice is compared to a separate table holding tax rates by dates and another one holding shipping rates by date and so forth, but why bother? It's not the '60s, memory is dirt cheap, and apps that are pushing the 2 gb limit of Access should probably be using something else anyway!

    Just my 2 cents worth!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    My issues regarding storing calculated values have nothing to do with disks. Disks are cheap - invalid data are not.

    If you know preceisly what you are doing then fine - you are denormalising your database to second normal form and there are sacrifices that come with that (especially with a desktop RDBMS like Access).

    My concern is that usually the first breath taken after teaching normalisation is how to denormalise a database and many (particularly the inexperienced) think that you can skip the normalisation part, or consider storing calculated values in their first pass at the logical design rather than in response to difficulties experienced when testing the physical database.

    I also store a few calculated values (there are good reasons that I believe justify this - chief amongst them are that the source data are immutable and the calculations are complex enough to not be practically redone each time they are required). I do not deny that there are times they are required however I don't believe this is one of them.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Poots ...

    About the only time where calc'd values NEED to be stored is in financial apps ... Technically, you can run aggregate queries to get the numbers you need but, try figuring the MTD, YTD, and possibly the QTD numbers on the fly. You're talking about a time crunch. Especially if your numbers are in multiple tables ... It's alot easier to have a table of accumulators that do just that. Then you hit that table to do your number crunching ... Besides, if the big accounting packages can do it why can't I????

    And you can normalize it too ... You can't ever normalize intent. You can normalize the structure ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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