Returning 0 value if count() is null

[scott0_1]

Hi all,



This report is going to be the end of me. I have a district with 12 offices in it. I want a user to be able to select a course (first aid for example) on a form then generate a report based on the number of employees that have had that course by office. The problem with my current query is that if an office has 0 employees that have taken the course, it does not show up on the report. I have a fancy stacked bar graph that makes the numbers easy to see, but the 0 office is not showing. I have tried a few things like an iif statement that checks if the count is 0, then make the value 0, but it doesn't work. I assume it has to do with a join, but I'm not sure how it should look. Here is the SQL statement I have right now that gets all the info:

SELECT tblOffices.ID, tblOffices.District, tblOffices.OfficeName, tblIndividualLearning.CatelogID, Count(tblIndividualLearning.EmpID) AS TotalDone, tblEmployee.Active
FROM tblOffices INNER JOIN (tblEmployee INNER JOIN (tblCourseCatelog INNER JOIN tblIndividualLearning ON (tblCourseCatelog.CatelogID = tblIndividualLearning.CatelogID) AND (tblCourseCatelog.CatelogID = tblIndividualLearning.CatelogID)) ON (tblEmployee.EmpID = tblIndividualLearning.EmpID) AND (tblEmployee.EmpID = tblIndividualLearning.EmpID)) ON tblOffices.ID = tblEmployee.Office
GROUP BY tblOffices.ID, tblOffices.District, tblOffices.OfficeName, tblIndividualLearning.CatelogID, tblEmployee.Active
HAVING (((tblOffices.District)=54) AND ((tblIndividualLearning.CatelogID)=[Forms]![frmStatsByCourse]![cbxCourses]) AND ((tblEmployee.Active)=True));

Thanks!!!

[June7]

Try changing the join type from INNER to LEFT or RIGHT (never can remember which) - the one that gives you "All records from tblOffices ..."

[scott0_1]

Try changing the join type from INNER to LEFT or RIGHT (never can remember which) - the one that gives you "All records from tblOffices ..."

I gave that a go, just the join from tblOffices to tblEmployees. It told me that I have an ambiguous out join. I hate those things!

SELECT tblOffices.ID, tblOffices.District, tblOffices.OfficeName, tblIndividualLearning.CatelogID, Count(tblIndividualLearning.EmpID) AS TotalDone, tblEmployee.Active
FROM tblOffices LEFT JOIN (tblEmployee INNER JOIN (tblCourseCatelog INNER JOIN tblIndividualLearning ON (tblCourseCatelog.CatelogID = tblIndividualLearning.CatelogID) AND (tblCourseCatelog.CatelogID = tblIndividualLearning.CatelogID)) ON (tblEmployee.EmpID = tblIndividualLearning.EmpID) AND (tblEmployee.EmpID = tblIndividualLearning.EmpID)) ON tblOffices.PortCode = tblEmployee.Office
GROUP BY tblOffices.ID, tblOffices.District, tblOffices.OfficeName, tblIndividualLearning.CatelogID, tblEmployee.Active
HAVING (((tblOffices.District)=54) AND ((tblIndividualLearning.CatelogID)=[Forms]![frmStatsByCourse]![cbxCourses]) AND ((tblEmployee.Active)=True));

[Micron]

AFAIK, the only distinction between a LEFT and RIGHT join is which side the "ALL" table is when viewing a table pair in the design view. The left to right order in design view affects the order of table referencing in sql. If you're real good at creating sql and know which table holds the ALL part, you don't really need design view. That's why I rely on the design view.

You will get that message when you try to link "downstream" tables with inner joins when the relationship between tables "upstream" can't be relied on to contain linkable fields (should have said 'can't be relied on to hold data on both sides of the join'). About the only way you can have tblA Right joined on B and have an inner join is to inner join C to A, but not C to B.

[June7]

Probably really need a dataset of all possible combinations of office/employee/course. I need to work with data to analyze requirements. If you want to provide db for analysis, follow instructions at bottom of my post.

[scott0_1]

I did finally get the right query. I can't even really post it because I made three different queries to merge into one... It's a bit much, but it gave me the data I needed.

Now when I try to graph the data, even with zeroes in the table, it doesn't graph the office if the total done = 0. Is this the right place to be asking about the char related to this data, or should I put another post up?