Find Matched/Unmatched Query (1 Viewer)

cb1

New member
Local time
Today, 07:56
Joined
Jan 21, 2021
Messages
5
I'm trying to define what I was hoping would be a fairly simple query in an access DB, but running into a few difficulties on the best way to achieve the result I am after. Basically I have 2x tables. In each table are 3x fields of interest, empName, code, accountnumber. What I need to do is join table A and table B based on the empName field, but I only want the query to return records where for the same empName, the combination of code and accountnumber is different in each table. So essentially table A empName equals table B empName AND tableA accountnumber does not equal tableB accountnumber AND tableA code does not equal tableB code.

I came up with somewhat of an alternative way to achieve this which is a bit time consuming - I have been joining in access on the empName column and then exporting the query results to MSEXCEL and then using an =IF type checker formula to look for mismatches on the code and accountnumber between the two tables.

As I need to do this type of analysis fairly regularly, trying to identify a way of achieving it a single query in Access without the export and excel part of the process would be helpful. I know the query wizard has the find unmatched template but I am not sure you can supply multiple conditions where one field matches between tables but others do not. It seemed fairly basic in what criteria you could supply during the process. I presume you have to use some for of group by and nested query approach but I wanted to see what suggestions and solutions or pointers you could offer.

thanks
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:56
Joined
Feb 19, 2013
Messages
16,553
Would think that is fairly straight forward
Code:
SELECT *
FROM TableA INNER JOIN TableB ON TableA.empname=TableB.empname
WHERE TableA.code<>TableB.code AND TableA.accountnumber<>TableB.accountnumber

if it is not, provide some example data from both tables and indicate what it is you would would want to be returned from that data
 
  • Like
Reactions: cb1

Users who are viewing this thread

Top Bottom