Access 2007. Sql query

[Sport Girl]

Hi everybody , I have this query in SQL using Access 2007:

 
SELECT Count(Opportunities.Won) AS CountOfWon, Count(Opportunities.Lost) AS CountOfLost, Count(Opportunities.[Cancelled/Postponed]) AS [CountOfCancelled/Postponed], Opportunities.System
FROM Opportunities
WHERE ((("Opportunities.Won")=True)) OR ((("Opportunities.Lost")=True)) OR ((("Opportunities.[Cancelled/Postponed]")=True))
GROUP BY Opportunities.System;
 
 

The problem is i get identical values for the three fields CountOfWon, CountOfLost, CountOfCancelled/Postponed.

Please what to do?

[MikeTheBike]

Hi

I assume all the feilds are yes/no, if so then perhaps this will give what you want, ie. the total of all 'Yes' values for each category?

SELECT Sum(IIF(Opportunities.Won,1,0)) AS CountOfWon, Sum(IIF(Opportunities.Lost,1,0)) AS CountOfLost, Sum(IIF(Opportunities.[Cancelled/Postponed],1,0)) AS [CountOfCancelled/Postponed], Opportunities.System
FROM Opportunities
WHERE ((("Opportunities.Won")=True)) OR ((("Opportunities.Lost")=True)) OR ((("Opportunities.[Cancelled/Postponed]")=True))
GROUP BY Opportunities.System;


??


MTB

[Sport Girl]

Thank u Mike u r the best