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:
Table1 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.
LINK TO SCREEN DUMPS https://drive.google.com/open?id=0B-5TRrOketfyb0tCbElYSWNSM1k
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]),"")