CODEWITHSUNDEEP
sqlms-accesscount
JT2013
JT2013

Reputation: 643

Count SQL in MS Access

I have a table called [Review Results] that looks somewhat like the following: 

[Reviewed By]....[Review Date]....[Corrective Action]....[CAR]
John.............1/1/2011.........yes....................yes
John.............2/5/2011.........No.....................yes
John.............2/24/2011........yes....................yes
Bobby............1/1/2011.........No.....................No
Bobby............3/1/2011.........yes....................No

I am trying to display the number of [Corrective Action] = yes by reviewer for a specified period and also the number of [CAR] = yes by reviewer for a specified period. I tried using the following SQL but it doesnt give the correct output: 

select 
[Reviewed By],
Count(IIF([Corrective Action] = yes, 1,0)) as [CAMBRs],
Count(IIF([CAR] = yes,1,0)) as [CARs]

from [Review Results] 

where [Review Date]  between #1/1/2011# and #3/1/2011#

group by
[Reviewed By]

Can someone point me in the right direction using SQL?

Upvotes: 3

Views: 698

Answers (2)

Arion
Arion

Reputation: 31249

Maybe something like this:

select 
   [Reviewed By],
   SUM(IIF([Corrective Action] = True, 1,0)) as [CAMBRs],
   SUM(IIF([CAR] = True,1,0)) as [CARs]

from [Review Results] 

where [Review Date]  between #1/1/2012# and #3/1/2012#

group by
[Reviewed By]

Upvotes: 1

Teja
Teja

Reputation: 13544

select 
[Reviewed By],
SUM(IIF([Corrective Action] = "yes", 1,0)) as [CAMBRs],
SUM(IIF([CAR] = "yes",1,0)) as [CARs]

from [Review Results] 

where [Review Date]  between #1/1/2012# and #3/1/2012#

group by
[Reviewed By]

Upvotes: 5

Related Questions