Calculate empty fields (isNull)?

[azhar2006]

I have a select query that calculates the Count of certain and gives me the result of the sum of each specialty. Some records are empty and there is no specific specialization. What I want is to calculate these empty fields as well.

SELECT tblMastr.adjAppo, Count(tblMastr.adjAppo) AS CountOfadjAppo
FROM tblMastr
GROUP BY tblMastr.adjAppo;

 

[arnelgp]

SELECT tblMastr.adjAppo, Count("1") AS CountOfadjAppo
FROM tblMastr
WHERE ((tblMastr.adjAppo) Is Null)
GROUP BY tblMastr.adjAppo;

 

[azhar2006]

SELECT tblMastr.adjAppo, Count("1") AS CountOfadjAppo
FROM tblMastr
WHERE ((tblMastr.adjAppo) Is Null)
GROUP BY tblMastr.adjAppo;

Thanks my friend for the quick reply. This is not what I mean. I have attached a database to you. Note in the table there are two employees who do not have any capacity. But the query shows that those who do not have the attribute are zero.

 

[arnelgp]

i used Union query (see the modified query).

 

[azhar2006]

i used Union query (see the modified query).

Thank you so much my dear friend . I am grateful to you

 

[Pat Hartman]

Did you try arnel's original solution? It should have worked.

Count(somefield) -- ignores records where somefield is null.

Count(*) -- the normal way which counts all rows returned by the query regardless of the value in any particular field. People mistakenly think that they have to put the name of the field they think they are counting in the Count() expression but Count() does NOT count individual values. It counts ROWS for the recordset or group by clause.

Count("1") is obtuse but should have worked.

 

[azhar2006]

Did you try arnel's original solution? It should have worked.

Count(somefield) -- ignores records where somefield is null.

Count(*) -- the normal way which counts all rows returned by the query regardless of the value in any particular field. People mistakenly think that they have to put the name of the field they think they are counting in the Count() expression but Count() does NOT count individual values. It counts ROWS for the recordset or group by clause.

Count("1") is obtuse but should have worked.

Thank you so much . You are very accurate, it worked well too.

SELECT TableA.Specialty, Count("1") AS CountOfSpecialty
FROM  TableA
GROUP BY TableA.Specialty;

 

[Pat Hartman]

If you do a count like this for a text field and you ever end up with two "blank" rows, one row will be the null values and the other row will be the ZLS (Zero Length String) values.