Solved

query - averages with different criteria

Posted on 2014-02-24
3
357 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
3 Comments
 
LVL 45

Expert Comment

by:Kdo
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now