Results 1 to 14 of 14
  1. #1
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25

    How to average two fields row by row and not the overall column average??

    I have a form that I would like to calculate a qualitative risk score but cannot seem to do so. I have one field "Risk Impact" and another field "Risk Probability" I would like to average these two fields in a third field "Risk Score" The values for each of the source fields will always be 1-5. No matter how I write the expression now, I get the total average for all records of the source field. I would like each row to show the average for only the fields in that row and not a total.



    Any tips?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    On a form, report or query, I would think this would work:

    (Field1 + Field2) / 2
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25
    Quote Originally Posted by pbaldy View Post
    On a form, report or query, I would think this would work:

    (Field1 + Field2) / 2
    I get invalid syntax message and the expression is missing a [,(, or | when I do that.

    Ive tried the avg command, sum command different separations through brackets or parenthesis & and + symbols...Im sure its something simple, but Im at a loss.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It would help to see exactly what you tried, and where. Avg() and Sum() are designed to work across records, not fields. You could also create a function similar to one of these:

    Minimum or Maximum Value Across Fields of Record
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25
    I dont understand why it is proving to be so difficult. I was able to effortlessly calculate date differences from field to field using the datediff expression. There must be a simple way to do this without VBA. Its such a simple task.

    Ive tried:

    =avg([Risk Impact], [Risk Probability])/2
    =avg(([Risk Impact], [Risk Probability])/2)
    =avg(([Risk Impact] + [Risk Probability])/2
    =avg(([Risk Impact] & [Risk Probability])/2
    =(Risk Impact) + (Risk Probability) /2
    =((Risk Impact) + (Risk Probability) /2)
    =(Risk Impact + Risk Probability) /2
    =((Risk Impact + Risk Probability) /2)

    And probably a few others too.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I assume this is the control source of a textbox? Either of your last two attempts should work, but because of the inadvisable spaces in your names you'll need to bracket the field names.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25
    Quote Originally Posted by pbaldy View Post
    I assume this is the control source of a textbox? Either of your last two attempts should work, but because of the inadvisable spaces in your names you'll need to bracket the field names.
    its a control source of an unbound text box marked in the table as a number field. As soon as I put the + sign in there for any statements it gives me the total average for all entries, just just the row I want.

  8. #8
    Join Date
    May 2010
    Posts
    339
    Try this

    =[field1]+[field2] /2

  9. #9
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25
    Quote Originally Posted by Access_Blaster View Post
    Try this

    =[field1]+[field2] /2
    that only divides field 2. If I put parentheses around the field names it averages all values, if I just put parentheses around the /2, it gives me an error.

  10. #10
    Join Date
    May 2010
    Posts
    339
    OK try again

    =Avg([1st]+[2nd])/2

  11. #11
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25
    Quote Originally Posted by Access_Blaster View Post
    OK try again

    =Avg([1st]+[2nd])/2
    nope, gives me a total average again.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post a sample db? I've tested and it worked as expected for me.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    jc1234 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    25
    Quote Originally Posted by pbaldy View Post
    Can you post a sample db? I've tested and it worked as expected for me.
    I figured out the problem. I must have not saved the table when I changed the two fields from text to number. As soon as I removed the avg function and changed those fields to number it worked perfectly! Thanks for all the help, sorry about that.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, glad you got it sorted out.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Average of multiple fields on one row
    By Lockrin in forum Access
    Replies: 3
    Last Post: 05-18-2010, 11:14 AM
  2. Query to Average on Grouped Fields
    By randolphoralph in forum Queries
    Replies: 1
    Last Post: 03-16-2010, 08:03 PM
  3. Conditioned Average
    By BravoEcho in forum Queries
    Replies: 1
    Last Post: 06-17-2009, 09:38 PM
  4. 7 day average and total
    By jannie in forum Access
    Replies: 1
    Last Post: 05-14-2009, 05:52 PM
  5. Replies: 0
    Last Post: 02-14-2007, 01:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums