Finding mismatches on the same table in MS Access


I'm a bit of a novice in MS Access but I've started doing some data validation at work and figured it was time to get down to a more simplified way of doing it.

First time posting, I'm having an issue trying to "only" display non-matching values within the same table i.e Errors

I have a table (query) where I have employee details one from one database and one from another. Both have the same information in them however there is a some details in both which are not correct and need to be updated. As an example see below:

Employee ID Surname EmpID   Surname1
123456789   Smith   123456789   Smith
654987321   Daniels 654987321   Volate
987654321   Hanks   987654321   Hanks
741852963   Donald  741852963   Draps

Now what I want to identify is the ones that are not matched by "Surname" and "Surname1"

This should be Employee ID

741852963   Donald  741852963   Draps
654987321   Daniels 654987321   Volate

I'm going to append this to an Errors table with I can list all the errors where values don't match.

What I've tried is the following:

Field: Matches: IIf([Table1].[Surname]<>[Table1].[Surname1],"Yes","No")

This doesn't seem to work as all the results display as Yes and I know for a fact there are inconsistencies.

Does anyone know what or how to do this? Ask any questions if need be.



Ok I think it might be better if I gave you all the actual names of the columns. I thought it would be easier to simplify it but maybe not.

Assignment  PayC            HRIS Assignment No  WAPayCycle
12345678    No Payroll      12345678            Pay Cycle 1
20001868    SCP Pay Cycle 1 20001868            SCP Pay Cycle 1
20003272-2  SCP Pay Cycle 1 #Error
20014627    SCP Pay Cycle 1 20014627            SCP Pay Cycle 1

So this gives and idea of what I am doing and the possible errors I need to counter for. The first one has a mismatch so I expect that to Error. The 3rd row has a Null value in one column and a Null in another however one is #Error where the other is just blank. The rest are matched.


This option handles Errors an Nulls in [HRIS Assignment No]:

SELECT * , IIf([Assignment]<>IIf(IsError([HRIS Assignment No]),"",Nz([HRIS Assignment No]​),""),"Yes","No") As Err
FROM [pc look up]
WHERE [Assignment]<>IIf(IsError([HRIS Assignment No]),"",Nz([HRIS Assignment No]​),"")