Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    6

    Cool Unanswered: Using Expressions-Not working on Table

    I have set a expression to sum fields in MS Access and it shows the sum of the fields on the Form, but when you go into the Table to view the same field it has zero's in the field instead of the sum. Is there anything special that you have to do in order for the expression to also work on the Table. Please help!!!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    My guess is your control ("field") on the form is not bound to the field in the table, hence the sum shows on the form but is not saved to the table. Most people will tell you that this is how it should be; calculated values should not be stored but rather re-calculated each time the form is viewed.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

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

    Talking

    Quote Originally Posted by Missinglinq
    My guess is your control ("field") on the form is not bound to the field in the table, hence the sum shows on the form but is not saved to the table. Most people will tell you that this is how it should be; calculated values should not be stored but rather re-calculated each time the form is viewed.
    Very true and I second that what Missinglinq said. There are RARE situations when some users have wanted to store derived values in a Table, but it is really regarded as a No-No.

    have a nice one,
    BUD

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

    Summed values

    Yes there are several situations where you would want to store the summed value in a table (i.e. a Purchase Order, Travel Voucher program or some other accounting type databases) where it is easier to get overall totals of several purchase orders/travel vouchers verses calculating each field every time you run a query or report on several records.

    Make sure also that the data type of the summed value you are storing is defined correctly (i.e. calculating .3 + .5 and storing this in a long integer field will give you 0!)
    Last edited by pkstormy; 04-10-06 at 17:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    easier isn't always a good reason to store such derived values.
    there are cicrumstances where I believ it is better to store such values, in things like tax calculations, or conversion rates, or other situations where you may have to have a legally justioficable evidence trail.

    just becasue its custom and prctise, or generally accepted it doesn't mean its infallable. generally speaking a normalised design is preferable, in fact with few exceptions its the RIGHT approach. it may be like breaking the law speeding in your car, sometimes it makes sense, sometimes the speed limit is plain daft, sometimes other things mean you need to beak the speed limit for reasons outside the car. Still you'd be really dumb to break the speed limit continuously, especially when PC plod is pointing his hairdryer or laser target marker at you, or you are driving by one of those roadside cameras.

    Its your application, only you can decide what is best for your users requirements, and your comfort zone.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    There is a variant on this problem, where the master is required to store the sum of values in a continuous subform. This can cause problems, for instance when Master/Child relationships are not defined or there are multiple subforms. One solution is to store the sum of the values in a hidden (unbound) field in the subform and then set a bound field in the master form equal to the value in the hidden subform field (whatever that is). This approach can also be useful in the problem described in the original post.

  7. #7
    Join Date
    Jan 2006
    Posts
    6

    Smile One Solution to the Problem

    The solution that I have found is to create a update query to update the field in the table when the form with the autosum field is closed. I am not sure if there are other solutions but it seems to be working great!!!

Posting Permissions

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