Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Posts
    32

    Question Unanswered: Sum fields in each form separately

    I am having trouble trying to sum fields in a form. Each form is a separate entry based on an inspection#. I have 25 fields (ex. [01Qty], [02Qty]. User will input values in all or some fields per inspection. I want to be able add each field and get a some for that inspection# only. I want the total to be put in field [QtyReject].

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ummm... you have 25 fields in each record for quantity?

    Read this.
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    May 2003
    Posts
    32
    Yes, there are 25 fields. Basically, there are 25 defects that a user can select from. It is unlikely that the user will input in all 25 fields. What I want to do is add all the defects from inpection. I do not want total of all inspections in the table.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Doesn't sound like you read that link...
    oh yeah... documentation... I have heard of that.

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

  5. #5
    Join Date
    May 2003
    Posts
    32
    I did read the link. I still don't understand how it trelates to my issue. I don't want to sum all of one defect. I want to add defect type quanitites together but limit it to a form by form basis not a total of the entire table.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you want to make life hard for yourself by not normalising the data then you could do somehting similar to:

    function SumNonNormalisedData() as integer
    SumNonNormalisedData=[01Qty]+[02Qty]+.........[0xQty]
    end function
    'you may need to add some validation code to handle null values
    'nz functon or event isnull may be worth a lookup....

    place a call to that function in your forms oncurent event (when a different recrod is displayed]
    and either the forms before update, or each of the relevant controls on change events

    its a nasty, nasty kludge that by normalising your data would have been a breeze
    HTH

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    In contrast, with NORMALIZED data, like in the link I listed...

    SELECT SUM([Defect]) FROM yourDefectTable t1 INNER JOIN yourOtherTable t2 ON t1.someKey = t2.someKey WHERE t1.someKey = Record you're evaluating

    Or a one-off on a form:

    DSUM("[defect]", "[yourDefectTable]", "[someKey] = " & yourKey)


    We're trying to make your life easier, it would behoove you to explore the options we're pointing you towards. You CAN do what you want to do the way you're suggesting if you have burning masochistic tendencies, but it's REALLY not recommended.
    oh yeah... documentation... I have heard of that.

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

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by salky
    I did read the link. I still don't understand how it trelates to my issue. I don't want to sum all of one defect. I want to add defect type quanitites together but limit it to a form by form basis not a total of the entire table.
    Imagine your data stored like this:

    Inspection#____Observation____Value
    23456_____________1__________23
    23456_____________2__________66
    23456_____________3__________12
    ..
    ..
    23456_____________23_________77
    23457_____________1__________52
    23457_____________2__________49
    ..
    .

    It becomes very easy to sum the value column using the methods suggested by Teddy. Specically, you can group data and also be selective so in your case you could have reports looking like:

    Inspection_____TotalValue
    23456___________379
    23457___________432

    or even just
    Inspection_____TotalValue
    23456___________379

    This is where the other writers are pointing you to with normalisation. Of course all you values have to be the same thing e.g. a series of temperature measurements. You can't do normailse if value1 is volume, value2 is temperature, value3 is weight. But then you wouldn't be adding them then anyway would you.

    Consider if someone comes along as say they want you to store 50 values. Are you going to add another 25 columns and then have to changed all your calculations ? If you do it the normalised way you don't need to change your table structure or your calculations (queries) .

    hth
    Chris

Posting Permissions

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