Difficult Query

[Guest]

I have a form "jobs" with subform "jobdetails" linked by [jobno].

each item in the subform has a [completed] tick box. And each entry for
jobno has a [jobcompleted] check box. I am trying to build a query that will
return a list of [jobno] that ALL its entries in jobdetails have [completed]
checked, but [jobcompleted] is not checked.

TIA

Rico

 

[Guest]

you can do it in two queries.

Make one query on the job detail table grouped on jobno and completed.

Make the second query using the query above and the job table

Group this query on jobno getting the max value from the query on field
completed and use the criteria yes on the query and no on the job table for
the completed fields.

 

[Van T. Dinh]

Try a Query with SQL like (untested)

SELECT J.JobID
FROM tblJob as J
WHERE J.[JobCompleted] = False
AND Not EXISTS
(
SELECT JD.JobDetailID
FROM tblJobDetail
WHERE JD.frg_JobID = J.JobID
AND JD.[Completed] = False
)

It is more efficient if you index both the [JobCompleted] and [Completed]
Fields.

 

[Guest]

however, this query will give results if there are no records in the job
details table for the job table.

Try a Query with SQL like (untested)

SELECT J.JobID
FROM tblJob as J
WHERE J.[JobCompleted] = False
AND Not EXISTS
(
SELECT JD.JobDetailID
FROM tblJobDetail
WHERE JD.frg_JobID = J.JobID
AND JD.[Completed] = False
)

It is more efficient if you index both the [JobCompleted] and [Completed]
Fields.

--
HTH
Van T. Dinh
MVP (Access)

I have a form "jobs" with subform "jobdetails" linked by [jobno].

each item in the subform has a [completed] tick box. And each entry for
jobno has a [jobcompleted] check box. I am trying to build a query that
will
return a list of [jobno] that ALL its entries in jobdetails have
[completed]
checked, but [jobcompleted] is not checked.

TIA

Rico

 

[Van T. Dinh]

True but then it is likely that a Job without JobDetails is invalid data and
it can be flagged off as completed.

Alternatively, an EXISTS criteria ( or DCount() ) can be added to the SQL I
posted if the O.P. doesn't want the JobID which doesn't have any related
JobDetail Records.