basic query question - one or multiple queries?

This is probably basic, but is stumping me. I am trying to program a dashboard, showing a variety of data points across our database.

For example, I have one table that shows quotes for a job (a job can have multiple quotes); I want to show, for a given job, whether there is an active quote (status field = 1), whether there is a quote that needs internal approval (another field), and whether there is a quote that needs client approval (yet another field).

Can this be done efficiently in a single query? I tried this in a single query using several sum(iif(XXX,1,0)) statements, but I was told this was 'deprecated' (i.e. not the right way to do it). Is there another way? Or do I need to write a whole bunch of very simple queries, and then for the dashboard tie them all together?

The problem, as you can see, if that I am going to a variety of child tables and each query has a slightly different scope/criteria...

Your thoughts are greatly appreciated!
terpsichoreAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
I would normally use conditional aggregates for that.  Again, the 'deprecated' part is news to me, I use it all the time but only have Access 2007.

If IIF is truly not to be used, you can look to use SWITCH:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_3527-A-SQL-Tidbit-Conditional-Aggregates.html

The other alternative that would come to mind, but is potentially less efficient would be to use sub-queries that did your secondary sums.

e.g.:
select sum(*) as condition1
     , nz((select sum(*) from your_table b where some_column = "condition 2"), 0) as condition2
from your_table a
where some_column = "condition 1";

Open in new window


The difficulty I see with that is if condition 1 doesn't exist, then you won't get the others.  Therefore, this would work if and only if you can always depend on one of the conditions always being available.
0
 
Kevin CrossChief Technology OfficerCommented:
I am not sure about the conditional aggregate being deprecated, but if you are looking for another way and each of the fields are flags, i.e., 1 or 0, then you can try like this:

select max(status) as status
     , max(internal_approval) as internal_approval
     , max(client_approval) as client_approval
from your_table;

Open in new window


M-1
0
 
terpsichoreAuthor Commented:
This is helpful for this scenario. How about a scenario where I am looking at a child table and want to sum records that meeting condition 1, condition 2, etc.? Can this be done using multiple aliases and a single query?
0
 
terpsichoreAuthor Commented:
very helpful
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.