By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,341 Members | 1,720 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

Returning Rows That Have No Count

P: 1
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?
Apr 29 '10 #1
Share this Question
Share on Google+
1 Reply

Jim Doherty
Expert 100+
P: 897
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblSites.Site, Count(tblSitesActivity.Site) AS [Count]
  2. FROM tblSites LEFT JOIN tblSitesActivity ON tblSites.Site = tblSitesActivity.Site
  3. GROUP BY tblSites.Site
  4. 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 :)
Attached Files
File Type: zip (9.3 KB, 48 views)
Apr 29 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.