How do I compare records of 2 access tables on multiple fields with concatenation rather than checki-CodePudding

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.

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

ID	NBR	TXT
800	402	Skyler

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.

CodePudding user response：

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