Querying table based on conditions of entries in linked table

[Guest]

I have a need to query an access database to find records where entries have
all been completed in a linked table but x field hasn't in the originating.

Data - Table 1
- intRefID
- strCompletedBy

Data - Table 2
- intRefID
- strDocReference
- strDocCompletedBy

There is a one to many relationship from Table1 to Table2 (it is possible
that Table2 may not contain any linked data also).

My query needs to return all results from Table1 where strCompletedBy IS
NULL, and ALL (if any) of the linked records in Table2 have strDocCompletedBy
filled in (therefore if there are two entries in table2 this would only
return the record in table1 IF both entries have strDocCompletedBy filled in).

Hope this makes sense, appreciate any help you can offer.

Many Thanks, Al.

 

[Marshall Barton]

I have a need to query an access database to find records where entries have
all been completed in a linked table but x field hasn't in the originating.

Data - Table 1
- intRefID
- strCompletedBy

Data - Table 2
- intRefID
- strDocReference
- strDocCompletedBy

There is a one to many relationship from Table1 to Table2 (it is possible
that Table2 may not contain any linked data also).

My query needs to return all results from Table1 where strCompletedBy IS
NULL, and ALL (if any) of the linked records in Table2 have strDocCompletedBy
filled in (therefore if there are two entries in table2 this would only
return the record in table1 IF both entries have strDocCompletedBy filled in).

I think this will do what you want:

SELECT table1.intRefID
FROM table1 INNER JOIN table2
ON table1.intRefID = table2.intRefID
WHERE table1.strCompletedBy Is Null
And Count(table2.strDocCompletedBy) = Count(*)
GROUP BY table1.intRefID