MS Access Count Query - Need a '0" Return Not Null?

Question

I have created this query to count the number of records for a particular office. The query works fine when there is a record for the office but returns a null value if there are no records. I need this query to return a zero value so those values combine corrrectly with my union all query.


SELECT Count(IP_All.Office) AS CountOfOffice
FROM IP_All
GROUP BY IP_All.Office
HAVING (((IP_All.Office)="MD - Acquisition Management"));

Please let me know how to do this. I appreciate your help. Thanks in advance.

Answer 1

Maybe you can try adding the Nz with a bracket before and an extra one after to get your desired results

Like this



SELECT Count(Nz(IP_All.Office)) AS CountOfOffice

FROM IP_All

GROUP BY IP_All.Office

HAVING (((IP_All.Office)="MD - Acquisition Management"));

Answer 2

Access Query Not Null

Answer 3

Try this with 2 queries:



qry1:

-----------

SELECT

IIf([Office]="MD - Acquisition Management",1,0) AS MD_AcMan,

IIf([Office]="Office2 name",1,0) AS Office2,

IIf([Office]="Office3 name",1,0) AS Office3

FROM IP_ALL;



qry2:

-----------

SELECT

Sum(qry1.MD_AcMan) AS SumOfMD_AcMan,

Sum(qry1.Office2) AS SumOfOffice2,

Sum(qry1.Office3) AS SumOfOffice3

FROM qry1;



Run query 2 to get what you want.



Sorry about editing my reply, i misunderstood the problem the first (and second) time.



Another way of doing it is to enter a Dummy record for each office in the IP_ALL table. So every office always has 1 entry then just subtract 1 from the count on your original query.

Answer 4

In question layout, click the Totals button on the toolbar. this may open the entire row interior the Fields grid. Then replace the Values column to be Sum. leave the Names column as team