Query Help

[Guest]

I've built a database to track volunteers and their assignments on a project.
I'm trying to build a query to select records from a "volunteers" table who
don't have an entry in the "ShiftAssignment" table. I tried to use an
"IsNull" criteria, but it didn't work because they don't have record in the
"ShiftAssignment" table to check for a null criteria. What is the correct
way to select those records from the database?

 

[John Vinson]

I've built a database to track volunteers and their assignments on a project.
I'm trying to build a query to select records from a "volunteers" table who
don't have an entry in the "ShiftAssignment" table. I tried to use an
"IsNull" criteria, but it didn't work because they don't have record in the
"ShiftAssignment" table to check for a null criteria. What is the correct
way to select those records from the database?

You can use the Unmatched Query Wizard, or you can "roll your own" as
follows:

Create a new Query. Join [Volunteers] to [ShiftAssignment] (or to a
query based on ShiftAssignment for a certain project or certain date
range, if you want to find volunteers who have been assigned in the
past but just not for this particular project).

Select the Join line and choose Option 2 (or maybe 3) - "Show all
records in Volunteers and matching records in ShiftAssignment".

Select whatever fields you want to see from Volunteers, and *only* the
linking field (VolunteerID?) from ShiftAssignment.

Put a criterion on this field of

IS NULL

This "frustrated outer join" query will find only those volunteers who
are NOT in the table (or query).

John W. Vinson[MVP]

 

[Guest]

Works like a champ. I never would have perceived that approach from my
manual or the help section. Thanks.