Minus in Access

[Guest]

Hi

At the moment I have two queries, one producing a full
list and the other a shortened list of the full list but
with the same fields.
I want to subtract the short list from the full list - a
MINUS I thought but I can't seem to do this in Access.
Is there a way around this? or do I have to do it in my
code once I have extracted the data from Access? (I'm
using stored queries.

Thanks in advance.

 

[John Viescas]

Access does not support a MINUS operation, but you can get the same result
by using an Outer Join on the keys combined with a Null test.

SELECT BigTable.*
FROM BigTable
LEFT JOIN SmallTable
ON BigTable.PKey = SmallTable.PKey
WHERE SmallTable.PKey IS NULL

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas

 

[Guest]

Hi

I actually have to use nested queries in my SELECT query,
will I still be able to use a LEFT JOIN. I get a syntax
error when I try to save the query.

Thanks in advance.

 

[John Viescas]

Please post the SQL. I can't help you figure it out without that.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas

 

[Guest]

Hi

I managed to put the nested queries in a separate query
and then SELECT that query and it now works.
Thanks for your help.