Reflecting zero record when counting from another table

[arnold71]

I have two tables. The first one holds the Project ID No. which is the primary key, Project Title, etc.

The second table holds the municipality that the project covers. Their relationship is one to many.

There's a probability that there is/are no record/s of municipality encoded for a certain project. I want to create a report, reflecting the no. of municipality covered for each project. Like:

ProjectIDNo. No. of Municipality covered
001-001 2
001-002 0
001-003 0
002-001 3

I used the count function but it only shows the project ID that has records to count (Project ID no. 001-001 and 002-001 only). How about the other 2 Project ID No. with no record? How to reflect zero if there's no record to count?

Thanks a lot in advance.

 

[Galaxiom]

Use an OUTER JOIN from Project to Municipality in the query so that all ProjectIDs are listed.

 

[Trevor G]

Although you have created a One to Many relationship in the Query you can set the Joint to a Left or Right Join this will then show what you want, the problem you then get is that because you will have all in one table the count will show 1 when you add the total. The way around that is to then use an Expression and and IIF Statement to show iif(fieldName=1, 0, fieldname). An example of the SQL code is shown below:

SELECT tblProject.Project, Count(tblProject.Project) AS [Count], IIf([Count]=1,0,[Count]) AS Tot
FROM tblProject LEFT JOIN tblProjectNAme ON tblProject.ProjID = tblProjectNAme.ProjectID
GROUP BY tblProject.Project;