Query to Count values from different columns

Question

I am looking for a query than can work in Access that gives me the total count of users who completed certain requirements. I know I can group by column, the "tricky" part is that I need to group in one column if the user completed any of the requirements from columns B,C or D. In other words, this is my data:

User    Company A   B   C   D   E
John    ABC     1               1
Bob     ABC     1   1   1       1
Reggie  ABC             1   1   1
Alex    BCA     1               
Mary    BCA     1   1       
Jane    CBA     1   1   1   1

And this is the end result I'm looking:

Company A   E   F(B or C or D)
ABC     2   3   2
BCA     2   0   1
CBA     1   0   1

Answer 1

You can use NZ (COALESCE in SQL) to make null values 0 then you can do "normal" work -- like adding them in this example:

 SELECT SUM(NZ(B,0)+NZ(C,0)+NZ(D,0)) AS F

or looking at your logic

 SELECT SUM(IIF(NZ(B,0)+NZ(C,0)+NZ(D,0) > 0,1,0) ) AS F

(nb, CASE WHEN THEN END would be used instead if IIF in SQL)

Answer 2

You would use group by:

select Company,
       nz(SUM(A), 0) as A, nz(SUM(E), 0) as E,
       (nz(SUM(B), 0) + nz(SUM(C), 0) + nz(SUM(D), 0) ) as F
from table
group by Company;

EDIT:

Hogan's point is good, and easily incorporated:

select Company,
       nz(SUM(A), 0) as A, nz(SUM(E), 0) as E,
       (nz(MAX(B), 0) + nz(MAX(C), 0) + nz(MAX(D), 0) ) as F
from table
group by Company;