can I create a reverse union ('exclusion') query? 1

[sharkboots]

Hello,

Does anybody know of a way that I can create a reverse union query - I wish to show all records that appear in one query EXCEPT the ones that appear in another? The two queries I wish to compare come from very different sources and I can't just reverse the second query's SQL and use it as a where clause.

 

[GHolden]

Hi,

This may help...

Assuming the the queries have a common unique key.

You can use NOT IN like this...

SELECT qryOne.ID,Field1,Field2
FROM qryOne
WHERE qryOne.ID NOT IN (SELECT qryTwo.ID FROM qryTwo)

This will select all ID, Field1, Field2 (include whatever fields you need) from qryOne where the ID field in not in the selection with in the brackets (ie. all the ID field from qryTwo).



There are two ways to write error-free programs; only the third one works.

 

[sharkboots]

Brilliant, I knew it must be simpler than I was trying to make it.

Thanks