Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Query (2003)

    I am trying to get a query (or a table field) to be a 6 month average. I have each month as a separate field in a table. How do I create a query or function to average these 6 fields together to get a field that is the 6 month average? I am not partial to where it gets done or how. It is just my previous 1 hour attempts have all failed miserably. Any help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Query (2003)

    Welcome to Woody's Lounge!

    A table design with each monthly value in a separate record (i.e. the monthly values below each other instead of next to each other) would have been a lot easier to handle.

    If there is a non-null value for each month in each record, you can calculate the average in a query based on your table: create a column

    MyAverage: ([Field1]+[Field2]+[Field3]+[Field4]+[Field5]+[Field6])/6

    where MyAverage is the name you want to give the calculated column, and Field1, ..., Field6 are the names of the fields. If there may be null values, you can use a custom function. See <post#=440550>post 440550</post#>.

  3. #3
    New Lounger
    Join Date
    Apr 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Query (2003)

    Oh man........ This would fall under the category of missing the obvious. Thank you for putting me out of my misery! Works great! I was so concentrated on using the average function that I total missed the easy way of doing it.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Access Query (2003)

    As Hans noted
    >>A table design with each monthly value in a separate record (i.e. the monthly values below each other instead of next to each other) would have been a lot easier to handle.<<

    you could then have used the ave function.

Posting Permissions

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