OK you've simplified the data a bit too much and your approach is never going to work.
First of all I've changed the ID field names to MainID, SurvID, CheckID for clarity
I've added additional records to tblMain - you now have TEST-01 through to TEST-04 and I've added corresponding records to tblSurv so that only TEST-01 & TEST-03 have zero depth values
These are identified using the query qryTestDepthZero
Code:
SELECT tblSURV.RECNAME, tblSURV.DEPTHFROM tblMAIN INNER JOIN tblSURV ON tblMAIN.RECNAME = tblSURV.RECNAME
WHERE (((tblSURV.DEPTH)=0));
I've included both tables in case your real tables have additional fields you need here....but this would work equally well: qryTestDepthZero _v2
Code:
SELECT tblSURV.RECNAME, tblSURV.DEPTH
FROM tblSURV
WHERE (((tblSURV.DEPTH)=0));
Now create an outer join query with tblMain and one of the above queries to list tests with no zero depth values qryTestNoDepthZero
Code:
SELECT tblMAIN.MainID, tblMAIN.RECNAME, "MISSING 0 DEPTH SURVEY" AS RESULT
FROM tblMAIN LEFT JOIN qryTestsDepthZero ON tblMAIN.RECNAME = qryTestsDepthZero.RECNAME
WHERE (((qryTestsDepthZero.RECNAME) Is Null));
NOTE: the added Result field with the wording you wanted.
This query has everything you need. You DON'T need tblChecks at all and using it will give you permanent records which may later become invalid
However, whilst NOT recommended, if you really MUST append records to tblChecks, use qryAppendTestsNoDepthZero:
Code:
INSERT INTO tblCHECKS ( RECNAME, RESULT )
SELECT qryTestsNoDepthZero.RECNAME, qryTestsNoDepthZero.RESULT
FROM qryTestsNoDepthZero;
See attached