Totals Query - Independent criteria

[scampbell]

Hello All,

I figured out this problem a while back, but overwrote it (sigh) and for the life of me cannot figure it out again. I'm creating a query of totals from a certain table (MASTER). This query will eventually just have one row of totals. Now, this is going to sound like a waste of a query as I believe this part can all be done directly in a report (the final end of this process) but there has been a request to save these totals to a table every time the query is run for historical purposes.

Anyway, the problem comes when I want to put a criteria on one of the counts without affecting the others. I believe I did this strictly by writing an expression before, but like I said can't recreate it. So let's say I have fields Index, PID, and SCOPE. For the first two, I just add them to the totals query and ask for a count, no big deal. For the third, SCOPE can either be "Yes" or "No" (or blank, in theory). I want the get a count of all of the items with "Yes" in the scope field, without it affecting the other two counts (I don't want separate counts of Index for "Yes", "No" and blank SCOPE).

SELECT Count(MASTER.Index) AS CountOfIndex, Count(MASTER.PID) AS CountOfPID, Count([MASTER].[SCOPE]<>"NO") AS Expr1FROM MASTER;

This is what I have tried among a million other things. This is probably the closest I have come, but the count it gives is for "Yes" or "No", omitting only NULL results. How can I change this to get only the "Yes" items?

Thanks in advance.

[June7]

Is SCOPE a Yes/No type?

Try:

Sum([SCOPE])

or

Sum(Abs([SCOPE]))

If it is text, try:

Sum(IIf([SCOPE]="Yes",1,0))

[scampbell]

Perfect! Much appreciated.