How to Implement SQL Intersect and Minus Operations in Ms Access

How can I implement SQL INTERSECT and MINUS operations in MS Access

INTERSECT is an inner join. MINUS is an outer join, where you choose only the records that don't exist in the other table.

INTERSECT

select distinct
  a.*
from
  a
  inner join b on a.id = b.id

MINUS

select distinct
  a.*
from
  a
  left outer join b on a.id = b.id
where
  b.id is null

If you edit your original question and post some sample data then an example can be given.

EDIT: Forgot to add in the distinct to the queries.

How to INTERSECT in MS Access?

For test data in a table named [MirrorTest]

pk  A      B    
--  -----  -----
 1  foo    bar  
 2  hello  world
 3  hello  there
 4  world  hello
 5  bar    baz  
 6  bar    foo

the query

    SELECT pk, A, B
    FROM MirrorTest
    WHERE A<=B
UNION ALL
    SELECT pk, B, A
    FROM MirrorTest
    WHERE A>B

will return all of the rows such that A<=B, i.e.,

pk  A      B    
--  -----  -----
 2  hello  world
 3  hello  there
 5  bar    baz  
 6  bar    foo  
 1  bar    foo  
 4  hello  world

Wrap that in an aggregation query to find the candidates for deletion, defined as the larger [pk] value where there are duplicates

SELECT Max(pk) AS pkToDelete
FROM
    (
        SELECT pk, A, B
        FROM MirrorTest
        WHERE A<=B
    UNION ALL
        SELECT pk, B, A
        FROM MirrorTest
        WHERE A>B
    ) AS u
GROUP BY A, B
HAVING COUNT(*) > 1

returns

pkToDelete
----------
         6
         4

so you could just use that in the WHERE clause of a DELETE query

DELETE FROM MirrorTest
WHERE pk IN
    (
        SELECT Max(pk) AS pkToDelete
        FROM
            (
                SELECT pk, A, B
                FROM MirrorTest
                WHERE A<=B
            UNION ALL
                SELECT pk, B, A
                FROM MirrorTest
                WHERE A>B
            ) AS u
        GROUP BY A, B
        HAVING COUNT(*) > 1
    )

Minus Query in MsAccess

One possibility is NOT IN. There is no such thing as a minus query in MS Access.

select h.* from hello h
WHERE uniqueid NOT IN
(select uniqueid from hello1 h1)

For a purely sql solution, you need, say:

SELECT t.* FROM Table t
LEFT JOIN NewTable n
ON t.ID = n.ID
WHERE t.Field1 & "" <> n.Field1 & ""
   OR t.Field2 & "" <> n.Field2 & ""

However, it is easier using VBA.

Implementing INTERSECT (INNER JOIN) in MS ACCESS

Haven't tested this so definitely you will have a lot of MS Access specific changes to be made. But I ask you not to obfuscate query by unnecessarily quoting each identifier in square blocks:

SELECT     DISTINCT title
FROM       (  
            SELECT [Catt].[Name] as title
            FROM   [Catt]
            WHERE  ([Catt].[Name] Like "*" & [forms]![Main]![SrchText] & "*") AND 
                    [forms]![Main]![SrchText] IS NOT Null AND 
                    [forms]![Main]![SrchText] <>''
           ) AS view1

INNER JOIN 

           (   
            SELECT   [Catt].[Name]
            FROM     [Catt]
            WHERE    [Catt].[Category] Like [forms]![Main]![Combo7].Value AND 
                     ([forms]![Main]![SrchText] IS Null OR 
                      [forms]![Main]![SrchText]='')
           ) AS view2 ON view1.title = view2.Name

ORDER BY   view1.title

But basically something like this does the trick..

Edited as Gordon pointed out

Subtracting Lists from Queries in Access

One method uses not in or not exists. Assuming that column A is unique, you can do:

select s.*
from (<Suche>) as s
where s.A not in (select d.a from (<DUN>) as d) and
      s.A not in (select h2.a from (<HOL>) as h2);

problem with intersect operation in a sql query

What database are you using? Are you sure that intersect is supported? I tried your query on Oracle (changing the table names to something corresponding to my DB) and it worked ok.

EDIT:
Since you confirmed you are using MS-Access, then it's clear that INTERSECT is the problem since it's not supported with MS-Access: http://www.access-programmers.co.uk/forums/archive/index.php/t-86531.html

EDIT2:
This is untested, but the basic idea is that you need to find all rows in your first query that exist in your second query. To do that, you will have to compare every column between the 2 queries for a match, as all the columns must match for it to be an "intersected" row.

There may be some syntax issues, but hopefully this gets you started.

SELECT r.col1
     , t.col1
     /* list all other columns here */
  FROM results r
     , types t
 WHERE r.a = t.b
 AND EXISTS (
     SELECT *
       FROM results r2
          , types   t2
      WHERE r2.c = t2.b
        AND NZ(r.col1,0) = NZ(r2.col1,0)
        AND NZ(t.col1,0) = NZ(t2.col1,0)
        /* list other columns here, they all need to match so intersection will work */
 )

Ms Access - show difference between two query results

This would be much easier if Access implemented all of the ISO SQL set operators (UNION, INTERSECTION, REMOVE) because what you want is the REMOVE operation (called MINUS in Oracle). However, like MySql MS-Access only implements the UNION operator. Fortunately, MySql has a doc page that explains how to implement the MINUS (REMOVE) operator functionality in SQL without the keyword

As it happens, you got pretty close, you just need to add an extra set of conditions to your NOT EXISTS query.

So something like this should work:

SELECT AllMFR.*
FROM AllMFR
WHERE NOT EXISTS(
    SELECT *
    FROM UniqueMFR
    WHERE UniqueMFR.ITEM_NUMBER = AllMFR.ITEM_NUMBER
      AND UniqueMFR.MFR         = AllMFR.MFR
      AND UniqueMFR.MFR_PN      = AllMFR.MFR_PN
    );

Minus/hyphen in table name in SQL query

Access automatically interprets a word break before -

This means:

SELECT * from MYTAB-10

Is equivalent to the following queries

SELECT * FROM MYTAB -10 -- Space added
SELECT * from [MYTAB] AS [-10] -- Explicit alias to make meaning more clear

This also means you could write the following

SELECT [-10].SomeField FROM MYTAB-10 INNER JOIN MYTAB-11 ON [-10].SomeField = [-11].AnotherField

As ADO automatically adds SELECT * FROM if you just provide a table name, that's equivalent.

However: NEVER use this. This behavior hasn't been standardized, and will make your code nonportable as other RDBMSes will syntax error on it.

Also note that this doesn't work in ADO when directly opening a table instead of using an auto-generated SQL statement:

Set rst = CurrentProject.Connection.Execute("MYTAB-10", Options := adCmdTableDirect)
'Errors: cannot find input table or query MYTAB-10

Also note: the + character exhibits similar behavior. However, \, : and * do not.

Writing a difficult query for MS-Access (without MINUS operation, 3 table join)

The query below is created from two MS Access queries with the SQL cut and pasted into derived tables. The two queries select the employee with their current training (#1) and the employee with their required training (#2). It is not updateable, but is easy to create. You can show only the missing training by adding a line at the end:

WHERE et.TrainingName Is Null

To select a single employee, you can add a line:

WHERE rt.EMPID=1 AND et.TrainingName Is Null

However, you mention a subform, so I expect you will wish to use link child and master fields to limit by employee.

SQL

SELECT rt.empid,
       rt.position,
       rt.trainingname,
       et.trainingname AS IsTrained
FROM   (SELECT employees.empid,
               requiredtraining.position,
               requiredtraining.trainingname
        FROM   employees
               INNER JOIN requiredtraining
                       ON employees.position = requiredtraining.position) AS rt
       LEFT JOIN (SELECT employees.empid,
                         employees.position,
                         employeetraining.trainingname
                  FROM   employees
                         INNER JOIN employeetraining
                                 ON employees.empid = employeetraining.empid) AS
                 et
              ON ( rt.trainingname = et.trainingname )
                 AND ( rt.position = et.position )
                 AND ( rt.empid = et.empid );