Re: Minus in Access

From: John Viescas (JohnV_at_nomail.please)
Date: 02/16/04 

Date: Mon, 16 Feb 2004 07:18:01 -0600

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
<rona@discussions.microsoft.com> wrote in message
news:1117b01c3f47c$3e6a9400$a601280a@phx.gbl...
> 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.

Relevant Pages

  • RE: Aggregates, Joins, I am totally stuck on this issue
    ... a left outer join it makes it an inner join? ... I have two aggregate queries which are grouped on ... >> query and then create an append query which puts the results of this master ... >> a LEFT OUTER JOIN and run it and what do it get? ...
    (microsoft.public.access.queries)
  • Re: Full outer join and Union again !!
    ... Choose the columns in the two queries so that the column order is the same. ... Union the two queries: ... are project_id and transaction_id and I have anoher table called budget ... > I have tried the left outer join assuming that my transaction table is the ...
    (microsoft.public.access.queries)
  • RE: Using q() to define a query
    ... queries tend to look something like the following. ... FULL OUTER JOIN jsequipment AS eq ON jsjourneys.equipment = eq.id ... JOIN jsports AS dp ON jsjourneys.departure_port = dp.id ... I'm concerned about $sql because when I output it to ...
    (perl.dbi.users)
  • Using q() to define a query
    ... My queries tend to look something like the following. ... FULL OUTER JOIN jsequipment AS eq ON jsjourneys.equipment = eq.id ... JOIN jsports AS dp ON jsjourneys.departure_port = dp.id ... However, I'm concerned about $sql because when I output it to Apache's debug log, it looks like this: ...
    (perl.dbi.users)
  • Re: Joins
    ... You need the equivalent of FULL OUTER JOIN, ... UNION ALL ... "Running Microsoft Access 2000" ... a Trial Balance & a Budget. ...
    (microsoft.public.access.queries)