Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2009
    Posts
    6

    Unanswered: Average Multiple Fields in a Form

    I need to average multiple fields in a form as we do mutiple tests for a single lot. In excel I would do it like this:

    Lot Number Moisture 1 Moisture 2 Average Moisture
    1234-5678 10.00% 12.00% 11.00%

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    welcome to the forums

    try setting the control source of the textbox on the form to
    = ([Moisture 1] + [Moisture 2]) / 2
    for example
    Me.Geek = True

  3. #3
    Join Date
    Mar 2009
    Posts
    6
    I want to get the average as tests are input. There are up to eleven tests total but sometimes less. I want the average after 1 and 2 have been performed as well as after 1, 2 and 3 have been performed and so on.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    You're going to have to help me out a little then.
    Quote Originally Posted by Teddy's FAQ
    Also, we do not know anything about your project or application unless you explicitly tell us about it. That means a question such as, "The client button doesn't populate the third insurance claim selector with the code!!!" will go unanswered. We don't know what you're talking about, sorry.

    In short, give us half a chance at actually being able to answer your question. Make it as easy as possible, and you're FAR more likely to get a response.
    What's the table structure? How are the values being entered? How is the form set up? What controls are involved? What's their names? And any other information necessary to answer your question is generally required
    Me.Geek = True

  5. #5
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    A more important question is Moisture 1, Moisture 2 etc. the actual moisture reading or an average? Also, will you be doing an Average overall?

    The reason I ask, is that it is mathematically incorrect to Average an Average.

  6. #6
    Join Date
    Mar 2009
    Posts
    6
    I had wanted to do a screen shot but cannot figure it out so instead I'll try to explain.

    We are producing coated grass seed. At every 2000 lb interval of produced seed we run a moisture test (we do this on the raw seed as well as the coated seed).

    The database has a table for "Coated Seed Quality Control" and columns in this table are: Raw Moisture 1, Raw Moisture 2, Raw Moisture 3, Coated Moisture 1, Coated Moisture 2, Coated Moisture 3, etc.

    Once 2000 lbs is produced let's say at 8:30 am, I want the production supervisor to do a moisture test and enter that data into the db form in the Raw Moisture 1 field. Then at 9 am when the second 2000 lbs is produced I want the production supervisor to do a moisture test and enter that data into the db form in Raw Moisture 2 field. I want the Average Moisture field to average tests 1 and 2 and each additional test as they are input. So once two samples have been taken I want the average of those two samples. Once three samples have been taken I want the average of those three samples and so on.

    I tried to use the following expression in the control source for the field titled "average raw moisture": =avg([Raw Moisture 1]+[Raw Moisture 2]+[Raw Moisture 3]+[Raw Moisture 4])

  7. #7
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Thank you for your explanation.

    I assume you will only have one average moisture field for the raw seed?

    On the update of the Raw Moisture 2 field you will need to enter the following code

    AverageMoisture = ([Raw Moisture 1]+[Raw Moisture 2])/2

    On the update of the Raw Moisture 3 field you will need to enter the following code

    Average Moisture field = ([Raw Moisture 1]+[Raw Moisture 2]+[Raw Moisture 3])/3

    Now do the same for the remaining Raw Moisture fields by adding the next field and increasing the divisor by 1

  8. #8
    Join Date
    Mar 2009
    Posts
    6
    can I write that code to occur/udpate automatically?

  9. #9
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Yes you can by using the After Update event of the relevant fields.
    e.g In the After Update event of the Raw Moisture 2 field you would enter
    Average Moisture=([Raw Moisture 1]+[Raw Moisture 2])/2

    When the Raw Moisture 2 field is updated, then Average Moisture field is also updated.
    Last edited by Poppa Smurf; 04-01-09 at 19:31.

  10. #10
    Join Date
    Mar 2009
    Posts
    6
    I entered that expression in the Event tab in the After Update field. I get a message that says: Microsoft Access can't find the macro 'Average Moisture = ([Raw Moisture 1]+[Raw Moisture 2])/2.'

    The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved. Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under.

  11. #11
    Join Date
    May 2005
    Posts
    1,191
    It's not a macro. I think Poppa Smurf was suggesting you put that in as code behind the AfterUpdate Event (click the dots in the After Update event field of the properties list, then choose code, paste what he wrote there). I think that should work.
    Me.Geek = True

  12. #12
    Join Date
    Mar 2009
    Posts
    6
    Thanks that worked.

    I had to enter it as a code not as an expression or macro

Posting Permissions

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