CODEWITHSUNDEEP
ms-accessmatch
Bassik
Bassik

Reputation: 13

Finding non-matches on 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:

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.

Thanks

UPDATE

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

Upvotes: 0

Views: 1927

Answers (3)

marlan
marlan

Reputation: 1485

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]​),"")

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

In your question you state "one from one database and one from another".
Assuming you start with two tables (you've shown us a query joining the four fields together?) then this query would work: 

SELECT  T1.[Employee ID]
        ,T1.Surname
        ,T2.EmpID
        T2.Surname1
FROM    Table1 T1 INNER JOIN Table2 T2 ON T1.[Employee ID] = T2.EmpID AND
                                          T1.Surname <> T2.Surname1
ORDER BY    T1.[Employee ID]

An INNER JOIN will give you the result you're after. A LEFT JOIN will show all the values in Table1 (aliased as T1) and only those matching in Table2 (aliased as T2) - the other values will be NULL, a RIGHT JOIN will show it the other way around.

Upvotes: 0

Rene
Rene

Reputation: 1093

This should work:

SELECT *
FROM Table
WHERE EmployeeID = EmpID
AND Surname <> Surname1
OR Len(Nz(Surname,'')) = 0
OR Len(Nz(Surname1,'')) = 0

Kind regards,

Rene

Upvotes: 0

Related Questions