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, CountOfCancelle d/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(Opportu nities.Won,1,0) ) AS CountOfWon, Sum(IIF(Opportu nities.Lost,1,0 )) AS CountOfLost, Sum(IIF(Opportu nities.[Cancelled/Postponed],1,0)) AS [CountOfCancelle d/Postponed], Opportunities.S ystem
FROM Opportunities
WHERE ((("Opportuniti es.Won")=True)) OR ((("Opportuniti es.Lost")=True) ) OR ((("Opportuniti es.[Cancelled/Postponed]")=True))
GROUP BY Opportunities.S ystem;


??


MTB

[Sport Girl]

Thank u Mike u r the best