By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 2,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Access 2007. Sql query

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

Expand|Select|Wrap|Line Numbers
  2. SELECT Count(Opportunities.Won) AS CountOfWon, Count(Opportunities.Lost) AS CountOfLost, Count(Opportunities.[Cancelled/Postponed]) AS [CountOfCancelled/Postponed], Opportunities.System
  3. FROM Opportunities
  4. WHERE ((("Opportunities.Won")=True)) OR ((("Opportunities.Lost")=True)) OR ((("Opportunities.[Cancelled/Postponed]")=True))
  5. GROUP BY Opportunities.System;
The problem is i get identical values for the three fields CountOfWon, CountOfLost, CountOfCancelled/Postponed.

Please what to do?
Mar 26 '08 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 634

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;


Mar 26 '08 #2

P: 42
Thank u Mike u r the best
Mar 26 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.