Ms-access – How to get a row count if the value is not null

ms-access

Using Access 2003

Table

EmpID Value1 Value2

001 100 
001       300
001 200   400
001 300   
001       250
001 200   400
002       100   
002 100   200
003 500   100
003
003 700
003

So on…,

I want to count (value1) and Count (value2) is not equal to null.

Expected Output.

EmpID Value1 Value2

001     4      4
002     1      2
003     2      1

So on…,

How to count the row value if it is not equal to null?

Need Query Help.

Best Answer

that seem to work?

SELECT Table1.Field1, Count(Table1.Field2) AS CountOfField2, Count(Table1.Field3) AS CountOfField3
FROM Table1
GROUP BY Table1.Field1;

and in your example, 003 is 2 and 1, not 1 and 0

result is:

 Field1 CountOfField2   CountOfField3
 1         4                4
 2         1                2
 3         2                1

which seem to be valid if field are NULL

Related Topic