We help IT Professionals succeed at work.

How can I perform a MINUS Statement in MS Access ??

kaber
kaber asked
on
Since MS Access do not support the MINUS statement, I am asking for a way how to get all recordset which is the difference from two select statements.

Example:

Select * from Orders
MINUS
Select * from Orders_Completed

I know that this statement works with Oracle SQL Plus, but
as far as I know this wont work with MS Access.

Thanks for all hints.

Binh
Comment
Watch Question

Select * from Orders where Primary Key NO IN (Select PrimaryKey from Orders_Completed)
should have been NOT IN:


Select * from Orders where Primary Key NOT IN (Select PrimaryKey from Orders_Completed)

Author

Commented:
Thank you for your hints, but since there are more than one primary key, I want to make it distinct to all Attributes not only the primary key.

Author

Commented:
Thank you for your hints, but since there are more than one primary key, I want to make it distinct to all Attributes not only the primary key.

Author

Commented:
Thank you for your hints, but since there are more than one primary key, I want to make it distinct to all Attributes not only the primary key.

what do you mean by "there are more than one primary key"?

THe Orders Table has a COMPOUND KEY?

Therre is no direct way, in Access, to use the MINUS forma as you have indocated.  MINUS is not a STANDARD SQL command, and has been added to Oracle's SQL/PLUS - which by the way id the Procedural extension of SQL as implemented in Oracle. (That is whey they called it ...PLUS - the PLUS means that they have extended Standard SQL).

What you can try is something along these lines:

Select * from Orders where Primary Key NOT IN (Select PrimaryKey from Orders_Completed where Secondary_Key = <specific value>)

and put that in a loop, so that you then go through all of the Secondary_Key values, one at a time.  That is the only way that you will accomplishe waht you are rtying to do, in Access.
Commented:
USE query wizard to create an UNMATACH query for you...

Commented:
Hi kaber,

This is done by an outer join.
The Unmatch will create one, but I prefer to make them myself in the editor to see the relation visually. It's too easy to have it "the wrong way..."
Just place both tables in your editor and drag/drop the keyfield(s) from the "main" table to the "optional" table.
now double-click the line(s) and select option 2 or 3 as needed.
Now place all maintable fields you need and the key of the optional table. For this key the criteria is filled with "Is Null" (without qoutes).
Thus only not matching main rows will show.

Nic;o)

Commented:
NICO that is an unmatch query using WIZARD instead of doing it manually....

Commented:
I know, just read:
>The Unmatch will create one, but I prefer to make them myself in the editor to see the relation visually.

Nic;o)

Commented:
:-E