How do I exclude data

[Guest]

I'm trying to build a query that looks at records with activity type,
"submitted", "reviewed" and "forwarded" for job requisitions. A new activity
record is created as each of the steps is completed. For example, before a
job can be "forwarded" it must be "reviewed" and before it can be "reviewed"
it must be "submitted" Once a job is "reviewed" and "forwarded" I don't want
them to show up in the query. Thus, I am only interested in job requisitions
with with the only activity "submitted" where "reviewed and "forwarded" are
null values.

 

[BruceM]

If the fields in question are yes/no fields, in query design grid set the
criteria for "submitted" to True and for the other two fields to false. If
they are text fields use the appropriate value ("submitted", I suppose) as
the criteria for that field, and Null as the criteria for the other two.

 

[John Spencer]

Two query approach.

Build the one query to get all the records that have actiivity types other
than "submitted". Then use the saved query in an unmatched query between it
and the table of jobs to eliminate all the jobs that do have other activity
types.

In one SQL statement that could look like the following.
Assumption:
-- Field and table names don't require brackets to deliminate them
-- Tables are Jobs and Activities and the relationship is one Job - many
activities
-- JobId the name of the two relating fields

SELECT J.*
FROM JobsTable as J LEFT JOIN
(SELECT JobID
FROM Activities
WHERE ActivityType <> "Submitted") as NotThese
ON J.JobID = NotThese.JobID
WHERE NotThese.JobID is Null