How can I make the Count function return 0?

Question

I am trying to create a series of queries in Access that count the number of clients of each ethnicity in each of 77 counties.

Here is SQL code for one of my queries that I'm trying to do this for...

SELECT [ClientsByCounty-Asian].County, [ClientsByCounty-Asian].CountyName, Count([ClientsByCounty-Asian].Asian) AS CountOfAsian
FROM [ClientsByCounty-Asian]
GROUP BY [ClientsByCounty-Asian].County, [ClientsByCounty-Asian].CountyName;

This query returns only 16 of 77 counties. I need it to display all 77 counties even when the result is zero and there are no records.

How can I get it to display the other 61 counties?

Answer 1

Assuming your 77 counties are stored in table called Counties:

SELECT  c.CountyName, COUNT([ClientsByCounty-Asian].County)
FROM    Counties c
LEFT JOIN
        [ClientsByCounty-Asian]
ON      [ClientsByCounty-Asian].County = c.County
GROUP BY
        c.County, c.CountyName