Calculations involving null records

[djdeuph]

Hi all,

I am attempting to create a dashboard that reports back the status of my project broken down by process. I have a form built for my startup page to display the following info:
# of controls # of tests completed # docs outstanding # remaining
Process
First, I have a table listing all of the controls by process.

My first query gets me the values for the first column:

SELECT Count(tblControlDesign.ControlID) AS CountOfKeyControls, tblScopeProcess.Process
FROM tblControlDesign INNER JOIN tblScopeProcess ON tblControlDesign.Process = tblScopeProcess.ScopeProcessID
WHERE (((tblControlDesign.[Key Control])="yes"))
GROUP BY tblScopeProcess.Process;

This works great. Next is the query to get completed tests:

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

I had to add the "is null" piece because otherwise the query would not even return processes where no testing had been completed.

I also do the same thing for another query to return the number of tests awaiting documents.

SELECT tblScopeProcess.Process, Count(tblTestPlan1.ControlID) AS CountOfOSDocs
FROM (tblControlDesign INNER JOIN tblScopeProcess ON tblControlDesign.Process = tblScopeProcess.ScopeProcessID) LEFT JOIN tblTestPlan1 ON tblControlDesign.ControlID = tblTestPlan1.ControlID
WHERE (((tblTestPlan1.DocumentationReceiveDate) Is Null) AND ((tblControlDesign.[Key Control])="Yes"))
GROUP BY tblScopeProcess.Process;

I then consolidate these results into one query (because of all of the joins) so that they can be used in my start form:

SELECT tblScopeProcess.Process, qryStart_KeyControls.CountOfKeyControls, qryStart_CompleteTests.CountOfCompleteTests, qryStart_OSDocs.CountOfOSDocs
FROM ((tblScopeProcess INNER JOIN qryStart_KeyControls ON tblScopeProcess.Process=qryStart_KeyControls.Process) LEFT JOIN qryStart_CompleteTests ON qryStart_KeyControls.Process=qryStart_CompleteTests.Process) LEFT JOIN qryStart_OSDocs ON qryStart_CompleteTests.Process=qryStart_OSDocs.Process;

The results of this query have blanks rather than zeros where there were no records for a given process. This prevents the calculation on the form from working:

=[CountOfKeyControls]-[CountOfCompleteTests]

Any ideas?

Derek

 

[HarleyQuinn]

Have a look at the Nz() function.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[T111]

Option A - You could always create a query and filter out where "field" "Is Not Null". That worked for me before
Option B - Maybe run a query that updates the table where Value for "field" is null set it equal to 0.

 

[HarleyQuinn]

Why make another query for something that a built in function will handle perfectly (as this is what it's designed to do)?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.