Count SQL in MS Access

Question

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?

You are counting all "no"'s also in your query.. Instead of count use sum.. that will work.. — Teja, Mar 29 '12 at 14:29

score 5 · Answer 1 · answered Mar 29 '12 at 14:27

5

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]

answered Mar 29 '12 at 14:27

Teja

13,214
36
93
155

i should mention that the **[Corrective Action]** and **[CAR]** fields are check boxes in the table **[Review Results]** – JT2013 Mar 29 '12 at 14:29
In that case you can use Abs([Corrective Action]) to return 1 or 0. `Count(Abs([Corrective Action] )` – Fionnuala Mar 29 '12 at 14:36

score 1 · Answer 2 · answered Mar 29 '12 at 14:30

1

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]

answered Mar 29 '12 at 14:30

Arion

31,011
10
70
88

this does not work....as mentioned above the **[Corrective Action]** and **[Car]** fields are check boxes in the **[Review Results]** table.... – JT2013 Mar 29 '12 at 14:34

Count SQL in MS Access

2 Answers2