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
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