Access query with Count()

Question

(Access 2010)

Can anyone tell the reason why column 2 and 3 retrieve exactly the same numbers? (they should not - different conditions and consequently different number of records)

Cannot see what's wrong...

SELECT tblAssssDB.[Division:], Count(([Mod New Outcome]="SELF EMPLOYED" And [ET Outcome]="GREY AREA")) AS [Undecided to Self-employed], Count(([Mod New Outcome]="EMPLOYED" And [ET Outcome]="GREY AREA")) AS [Undecided to Employed], Count(([Mod New Outcome]="SELF EMPLOYED" And [tblAssssDB].[ET Outcome]="EMPLOYED")) AS [Employed to Self-Employed], Count((IsNull([ET Outcome] And [ET Outcome]=[Mod New Outcome]))) AS [No change in Outcome]
FROM tblAssssDB
WHERE (((tblAssssDB.[ET Comment]) Is Not Null))
GROUP BY tblAssssDB.[Division:];

Help!!

Answer 1

Count is not really intended to be used to give you a quantity of specific outcomes. I think that you would be better served by using something like this

SUM(IIF(critiera, 1, 0))