Access 'Not Equal To' Join

Question

I have two tables, neither with a primary id. The same combination of fields uniquely identifies the records in each and makes the records between the two tables relate-able (I think).

I need a query to combine all the records from one table and only the records from the second not already included from the first table. How do I do this using 'not equal to' joins on multiple fields? My results so far only give me the records of the first table, or no records at all.

Please post your queries that you mention showing only results from the first table or no records at all. — Alexander Remesch, Feb 09 '16 at 20:45
SELECT ECDSlides.[Supplier Code], ECDSlides.[Supplier Name], ECDSlides.Commodity FROM ECDSlides LEFT JOIN (ECDSlides.Commodity = [Mit Task Details2].Commodity) AND (ECDSlides.[Supplier Code] = [Mit Task Details2].[Supplier Code]) WHERE (((ECDSlides.[Supplier Code])<>[Mit Task Details2].[Supplier Code]) AND ((ECDSlides.Commodity)<>[Mit Task Details2].[Commodity]) AND ((ECDSlides.[Baseline ECD])<>[Mit Task Details2].[Baseline ECD])); — Quasimodo, Feb 09 '16 at 22:15
Possible duplicate of: http://stackoverflow.com/questions/30987844/ms-access-query-to-display-rows-of-table1-which-are-not-in-table2-using-two-fiel?rq=1 — Alexander Remesch, Feb 10 '16 at 11:40

phenxd · Answer 1 · 2016-02-11T15:42:21.313

0

This might be what you are looking for

SELECT fieldA,fieldB FROM tableA
UNION
SELECT fieldA,fieldB FROM tableB

Union should remove automatically. 'Union All' would not.

If, for some reason, you get perfect duplicates and they are not removed, you could try this :

SELECT DISTINCT * FROM (
    SELECT fieldA,fieldB FROM tableA
    UNION
    SELECT fieldA,fieldB FROM tableB
) AS subquery

edited Feb 11 '16 at 15:42

answered Feb 09 '16 at 19:28

phenxd

689
4
17

Union won't work - it will duplicate values of tableA. TableA is a sub-set of TableB, with an additional identifying field attached. – Quasimodo Feb 09 '16 at 22:01
Are you sure, did you try? "Union will remove duplicates. Union All does not." http://stackoverflow.com/questions/4127714/remove-duplicates-from-sql-union – phenxd Feb 11 '16 at 15:38
Perfect! I did not know the distinction between Union and Union All. Many thanks. – Quasimodo Feb 11 '16 at 16:05

score 0 · Answer 2 · answered Feb 10 '16 at 11:38

Try the following:

SELECT ECDSlides.[Supplier Code], ECDSlides.[Supplier Name], ECDSlides.Commodity 

FROM ECDSlides LEFT JOIN (ECDSlides.Commodity = [Mit Task Details2].Commodity) AND (ECDSlides.[Supplier Code] = [Mit Task Details2].[Supplier Code]) 

WHERE [Mit Task Details2].Commodity Is Null;

Access 'Not Equal To' Join

2 Answers2