Return where condition does not exist

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know this must be possible, but so far I've been unsuccessful and can't
find a similar post.

Have a table full of 'problem details' and a table full of tasks.
- Table: ProblemRecords
- Table: TaskRecords

Entries within these tables are linked via (ProblemId) from ProblemRecords
and (ProblemID) from TaskRecords. This is a 1 to many relationship, e.g. one
problem can have many tasks.

I want to run a query that brings out any problem record that doesn't have a
task type of "Due Date" (Task type = strTaskType)

Therefore:
Show all ProblemID, where an entry does not exist within the strTaskType
field(from within the TaskRecords table) = "Due Date". Obviously the
TaskRecords table will not necessarily have any entries for the problem ID so
if no entry exists these should come out as well as where no specific Due
Date task exists.

As always any help would be greatly appreciated.

thanks, Al. ( (e-mail address removed) )
 
***Untested SQL String****
SELECT PR.*
FROM ProblemRecords AS PR
LEFT JOIN TaskRecords AS TR
ON PR.ProblemID = TR.ProblemID
WHERE (TR.strTaskType = "Due Date")
AND (TR.ProblemID Is Null)

HTH
Van T. Dinh
MVP (Access)
 
Back
Top