What is the opposite to an "Union All" clause in MS Access?

Hi All!

I have two queries with the same fields.  The first one returns 1000 records; the second one, 100 records whose Id's intersect with some of the first query.  What I want to do is to perform a query that shows the records of the first query MINUS the ones of the second query.  In fact, what I'm looking for here is the opposition of an Union All clause.

I tried this ("Not In" clause) before, but it didn't return any record.  Weirdly enough, an "In" clause works fine, nut not a "Not In" clause.  

SELECT [Équipement total].MODEL_NO
FROM [Équipement total]

where  [Équipement total].MODEL_NO

not IN

(SELECT [Équipement à exclure].MODEL_NO
FROM [Équipement à exclure])
order by MODEL_NO

Would anyone have a solution to this simple problem?

Thanks!

Francis
AlainSt-PierreAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
copy and paste this to the SQL view of the query

SELECT [Équipement total].MODEL_NO
FROM [Équipement total] Left Join [Équipement à exclure]
On [Équipement total].MODEL_NO= [Équipement à exclure].MODEL_NO
Where [Équipement à exclure].MODEL_NO is null
0
 
AlainSt-PierreAuthor Commented:
Hi capricorn,

I'm glad to read from you once again!  Unfortunately your query isn't returning any result. FYI I joined the results of my two queries.

Thank you!

Francis
My-Queries.docx
0
 
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the db.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try a Right Join:

SELECT [Équipement total].MODEL_NO
FROM [Équipement total] RIGHT JOIN [Équipement à exclure]
On [Équipement total].MODEL_NO= [Équipement à exclure].MODEL_NO
Where [Équipement total].MODEL_NO is null
0
 
AlainSt-PierreAuthor Commented:
Cap's query was just fine.  I closed the db, reopened it then it worked fine.  It seems like the damn thing's got a hard time saving new modifications.  Thanks a lot guys!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.