Solved

query - averages with different criteria

Posted on 2014-02-24
3
360 Views
Last Modified: 2014-02-24
Dear experts -
I have a workhorse table of expense items. Each item has a person, an action code, rate paid, language, etc.

Now, I need to generate a relatively long list of AVERAGES, by LANGUAGE - for example, for action 'x' where another field = y, for action 'z' where another field has a different value, etc.

Is there some way to do those other than creating a separate query for each average needed?

The point is - it would be easy if the criteria for the underlying dataset were identical (e.g., do an average of field x, then field y, and then field z where all the records have the following criteria...). But that's not the case.

Can I use some sort of IIF or other construct that would allow me to do this?

Thanks!
0
Comment
Question by:terpsichore
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 39882393
Hi terp,

Can you give us a bit more detail about what you're trying to do?  Perhaps an example?

And are you looking for a solution that runs in MS Access or on another engine.  The SQL can be quite different.


Kent
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39882476
You could use SUM / COUNT, where you use a case-statement to specify criteria (note: I used NULLIF to replace zero with null, so you don't divide by zero; you may add more and/or in the case-statement), example:
select	avg1 = SUM(case when cond1 = 1 then value end) / NULLIF(COUNT( case when cond1 = 1 value end),0)
,	avg2 = SUM(case when cond2 = 2 then value end) / NULLIF(COUNT( case when cond2 = 2 value end),0)
from	workhorse 

Open in new window

0
 

Author Closing Comment

by:terpsichore
ID: 39882630
I think this should be perfect. thank you.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question