?
Solved

query - averages with different criteria

Posted on 2014-02-24
3
Medium Priority
?
367 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 46

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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

621 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