Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010

    Unanswered: query to get yearly average and specific year data

    I'm a bit stumped on how to create a query that will give me the average of a column for a specified range of years by location and the data by location for the current (or other) specified year.

    My table (Weather) looks something like this:

    Location, Year, Temperature

    MS, 2007, " "
    IA, 2007, 86
    TN, 2007, 92
    MS, 2008, 96
    IA, 2008, 89
    TN, 2008, 93
    MS, 2009, 94
    IA, 2009, -999
    TN, 2009, -999

    I have a buddy that gave me the SQL for the Average part:

    "avg(case when TEMPERATURE != -999 then TEMPERATURE else null end) TEMP_AVG,"

    What I'm wanting to do is convert this to work in Access and specify the years I want to average (YEAR >=2007 AND YEAR <=2008).

    I also want to be able to query a specific year's temperature (YEAR=2008).

    Is this possible from one query? I'm pretty sure I'll have to use nested Iff's for the average to work with the null and -999 values, just not sure on the syntax. Also don't know how to work with the year ranges without getting some "not part of an aggregate function" error.

    Any help/insight will be greatly appreciated!

  2. #2
    Join Date
    Nov 2010
    I'm not sure because of my poor English that I understand, is this what you want it attached?
    Attached Files Attached Files

  3. #3
    Join Date
    Dec 2010
    I think the zip file was corrupt. Wouldn't let me open it.

Posting Permissions

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