We help IT Professionals succeed at work.

Exclude Records in a Table from a query

bellegigi
bellegigi asked
on
Medium Priority
428 Views
Last Modified: 2008-02-26
Hi. Does anyone know how to exclude records in a query from another query?

i.e. - I have a query called qryGetActiveServiceSupportFee and I have a query called
qryActiveAssociatesIndividualsFundCorps. I want to create a new query that has all of the records in the latter, without those in the first.

Help!

Thanks!
Comment
Watch Question

Commented:
For this you'll need a so-called "outer-join".
To keep things "clear" just create a new query based on your two mentioned above in the editor.
Now drag and drop the keyfield(s) to get the join.
Double-click this relation and select option 2 or 3, forcing qryActiveAssociatesIndividualsFundCorps to appear always and the other only when available.
Now place the keyfield of the qryGetActiveServiceSupportFee and test for "Is Null" (without quotes!). Thus only non-matching rows will appear.
Clear ?

Nic;o)

Commented:
When you create a new query, select the option "Find Unmatched Query Wizard". This should give you exactly what you need....painlessly.
Frédéric MétrauxFreelance Project Development Manager

Commented:
Supposing ID is the key field of both queries, I would write something like:

select id, f1, f1 from query1
where id not in (select id from query2)

This is rather slow, but it works.

Commented:
Charityg,

The wizard can be "painless", but won't support multiple outer-joins. Learning it from the editor will enable you to see the direction (LEFT or RIGHT) by an arrow and doesn't limit you to do it for just two tables.
I find that easier and more flexible as the "text" version of the wizard....

Nic;o)

Author

Commented:
I don't want the end result to include any records in the second query. Doesn't the first answer here leave it to where it will include the records if they match? I don't want to include them at all. I want them taken out of the first query. Thanks!

Commented:
Check my comment:
>>>Now place the keyfield of the qryGetActiveServiceSupportFee and test for "Is Null" (without quotes!).
>>> Thus only non-matching rows will appear.

Nic;o)

Author

Commented:
So, that will keep out everything in the second query? The numbers aren't matching up... that's why I am confused.

Commented:
Just check with a test on a small subset.

The numbers can differ from what you expect when the "join key field" is NOT unique...
Sample:
Tab1:
1 a
1 b
2 a
Tab2:
2 x
2 y
3 z
Will give from Tab1 -> Tab2:
1 a
1 b
Will give from Tab2 -> Tab1:
3 z

Clear ?

Nic;o)

Commented:
Have you tried using the unmatched query wizard?????

Author

Commented:
So, if their are duplicates in the join field, this will cause issues? The join fields are not unique in either table... any suggestions now? :) Thanks a bunch!
Commented:
The outcome will be correct, the count however might look funny.
The Tab2 if the Tab2 -> Tab1 outerjoin has 3 rows, although Tab1 has jus 1 row with a "2", these three are "reduced" to one "hit"

Tab2:
2 x - null on tab1
2 y - null on tab1
3 z

This is crrect, but the counting looks "funny"

Clear ?

Nic;o)

Author

Commented:
Thanks again!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.