I am trying to run a query where the result I am trying to get is to produce
records that are not present in an additional table in my query. What
parameters do I need to set to produce such information.
A "Frustrated Outer Join" query is the ticket here. The Query Wizard
offers it under the name of the "missing values query" or something
like that.
You can build it yourself too. Let's say you have TableA linked to
TableB by ID; and you want to find all TableA records for which there
are no corresponding records in TableB.
First create a query joining TableA to TableB by ID. This will get you
just those records which *do* have a match - not what you want! - but
be patient, there's two more steps!
Select the Join line in the query grid and select option 2 (or 3) -
"Show all records in TableA and matching records in TableB". This will
now show you all records in TableA, whether they have matches or not.
Fields in TableB will be shown if they exist, and will be NULL if
there is no record.
Finally, select *only* the ID field from TableB and put a criterion on
it of
IS NULL
This will limit retrieval to only those TableA records which do *not*
have a match.
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps