Returning Rows That Have No Count

[dfeilmeier]

I am attempting to run a query that will return all rows with their counts, even if they do not have a count based on the criteria. Right now the query is excluding the rows that have no counts. It currently returns results that look like this:

Site Count
A 5
C 4
D 1

And I would like to get results like this:
Site Count
A 5
B 0
C 4
D 1
E 0

Any thoughts?

[Jim Doherty]

Returning rows

Take a quick look at the SQL behind the query in the small I have attached for you.

Look how it is counting on sites set against a table of activities where some sites are not in the activity table. It follows your given example and might answer your question.

SELECT tblSites.Site, Count(tblSitesActivity.Site) AS [Count]
FROM tblSites LEFT JOIN tblSitesActivity ON tblSites.Site = tblSitesActivity.Site
GROUP BY tblSites.Site
ORDER BY tblSites.Site;

If you are not too familar with SQL it is showing ALL sites from one table that contains all the sites. It counts on records where there exists records and will include the 'zero' count where there does not exist records because we have asked the query to show all records in the sites table (you can see this if you look at the direction of the arrow head on the line that joins between the two tables in the Access query window)

Welcome to Bytes :)