I.T. Discussion Community!
-Collapse +Expand
Search Access Group:

-Collapse +Expand Access To/From
-Collapse +Expand Access Store

Prestwood eMagazine

August Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyMicrosoft Access BoardMS Access Interactive Topic   Print This    All Groups  

Query help

Query help in MS Access Interactive topic (part of our Microsoft Access group).

Quick Search: Query help  
Most Recent Post
Ankeny, -- UNK
Hope this is the right section to post this in.
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).  My dataset will include temperature data for the past 50 years, but I want to average the temperature for a specified 10 period and return the temerature for a specific year.  I have form that will ask for a year.  Code behind for will take that year and calculate the 10 years previous.  I hope to then oreturn the temperature for the year the user specified as well as the 10 year average temperature.

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!
 Posted 10 years ago (Thread Starter)
Comment Quote
About Wade.G -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #14406, No Replies Be the first!
Thread Started 12/13/2010 7:42:23 AM
View Counter=2926
Location=Ankeny, -- UNK 
Joined=10 years ago   MB Posts=2  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.

Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1124A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #14406 Counter
Since 12/13/2010

Follow PrestwoodBoards on: 

©1995-2020 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech