Getting a query to return all rows even if there is no data 2

[djdeuph]

Hi all,

I have a query where I want to list all of my processes and the number of completed tests per process. If no tests have been completed, I want the process to be listed with a zero in the column on number of tests completed. My query so far:

SELECT tblScopeProcess.Process, Nz(Count(tblTestPlan1.ControlID),0) AS CountOfCompleteTests
FROM (tblControlDesign RIGHT JOIN tblScopeProcess ON tblControlDesign.Process = tblScopeProcess.ScopeProcessID) LEFT JOIN tblTestPlan1 ON tblControlDesign.ControlID = tblTestPlan1.ControlID
WHERE (((tblTestPlan1.TestCompleted)=Yes))
GROUP BY tblScopeProcess.Process;

As you can see, I attempted to use the Nz function to get zeros instead of Nulls but that did not work. I think the issue is the fact that the table value in the TestCompleted field defaults to No (as opposed to Null or empty) when a test is not completed.

Any ideas?

 

[dhookom]

What happens if you remove the criteria? How about changing it to

WHERE tblTestPlan1.TestCompleted=Yes Or tblTestPlan1.TestCompleted is Null

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

In any outer join (LEFT or RIGHT), placing a constraint on the right table (in a LEFT JOIN) or the left table (in a RIGHT JOIN), has the effect of converting the JOIN to an INNER JOIN.

In your case

WHERE tblTestPlan1.TestCompleted =Yes

has the effect of eliminating those records from the first (RIGHT) join that do not meet the constraint. Specifically

- Records that do not have a match to the first join because "TestCompleted" will be NULL.

- Records that do have a match but "TestCompleted" is not "Yes".

You might try a coordinated sub-query

SELECT SP.ScopeProcessID, 

       (Select Count(*) From tblTestPlan1 As TP
        Where CD.ControlID = TP.ControlID
          AND TP.TestCompleted = Yes) AS CountOfCompleteTests

FROM tblScopeProcess As SP 
     LEFT JOIN tblControlDesign As CD ON CD.Process   = SP.ScopeProcessID 

WHERE TP.TestCompleted = Yes

GROUP BY SP.ScopeProcessID;

I added a few aliases and changed "SP.Process" to "SP.ScopeProcessID" since that's the field name in the join.

I also swapped tables in the first join to make a LEFT JOIN ... I just don't think in RIGHT JOIN terms.

 

[PHV]

Golom, your WHERE clause is irrelevant.
djdeuph, what about this ?

SELECT S.Process, Count(T.ControlID) AS CountOfCompleteTests
FROM (tblScopeProcess AS S
LEFT JOIN tblControlDesign AS C ON S.ScopeProcessID = C.Process)
LEFT JOIN (SELECT ControlID FROM tblTestPlan1 WHERE TestCompleted=Yes
) AS T ON C.ControlID = T.ControlID
GROUP BY S.Process

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

PHV

It may be incorrect (although it looks OK to me), ... but irrelevant?

Are you saying that it could be eliminated and the query would work the same way?

 

[PHV]

Golom, your WHERE clause references a table (TP) not in the FROM clause and thus is incorrect.

 

[djdeuph]

Thanks everyone!

Changing the joins around was the right thing to do. I tried the code as suggested by PHV and it seems to be working. Thanks for the quick response!