Search code examples
sqlms-access

How do I compare records of 2 access tables on multiple fields with concatenation rather than checking for Null fields

I am comparing two tables and trying to get returned the differences between them.
The [ID] field however should be ignored. I have tried the following but that did not return any results.

enter image description here

SELECT [x].[NBR] & [x].[TXT] AS [KEY], x.*
FROM (SELECT * FROM A)  AS x LEFT JOIN (SELECT * FROM B)  AS y ON (x.[NBR] = y.[NBR]) 
AND (x.[TXT] = y.[TXT])
WHERE (([x].[NBR] & [x].[TXT])<>([y].[NBR] & [y].[TXT]))

The desired outcome of above example would be:

ID NBR TXT
412 402 Skyler

or

ID NBR TXT
800 402 Skyler

or

NBR TXT
402 Skyler

I am using concatenation of fields rather than checking for Null fields because I am applying this on much bigger tables called from VB which results sometimes in a SQL Too complex error while the above does not.

It works on tables for which I am comparing all fields, but cannot get it to work when omitting a fields as in the example above.

Solution

  • You can use a subquery to find the matches, then rule these out with a Left Join:

    SELECT 
    A.ID, 
    A.NBR, 
    A.TXT 
FROM 
    A 
LEFT JOIN 

    (SELECT A.ID
    FROM A 
    INNER JOIN B 
    ON (A.TXT = B.TXT) AND (A.NBR = B.NBR)) AS AB

    ON A.ID = AB.ID
WHERE 
    AB.ID Is Null

    Output:

    ID  NBR TXT
412 402 Skyler