SQL in MS-Access: Using COUNT, JOIN and returning 0s

Question

Hi folks,

Apologies for posting this but although there are a few examples on the site, I just can't get mine to work.

So I have two tables as follows:

A Telephony table

ID | Name | GradeID
1    Richard   1
2    Allan     1
3    Peter

I also have a Grade table:

ID | Name
1    1
2    2
3    3
4    4
5    5

Anyway I'm trying to use COUNT() and LEFT JOIN to find out the number of times each grade is found in the Telephony table, including returning any which are 0, by using the following query:

SELECT telephony.GradeID, COUNT(*) AS Total
FROM telephony LEFT JOIN grade 
ON telephony.GradeID = grade.ID
GROUP BY telephony.GradeID
ORDER BY 1;

This query returns all found but will not return all grades with 0 entries:

Grade | Total
1       2

Please help. I'm using Microsoft Access 2003.

---

Thanks for all your help. That's working great.

However, when I try to incorporate a DATE BETWEEN it returns only the grades found again.

Any ideas?

Thanks

Answer 1

As only Grade table contains all available IDs you should use grade.ID in your SELECT instead of telephony.GradeID. Try to execute this query:

SELECT grade.ID, COUNT(telephony.ID) FROM grade
LEFT JOIN telephony ON telephony.GradeID = grade.ID
GROUP BY grade.ID
ORDER BY 1;

Answer 2

I think this is what you are looking for:

SELECT grade.ID, Count(telephony.ID) AS CountOfID
FROM grade LEFT JOIN telephony ON grade.ID= telephony.GradeID
GROUP BY grade.ID
ORDER BY 1;

BTW: "Name" is a very bad name for a column since it is a reserved word.