Limiting Find Unmatched to Same Record in 2 Tables 1

[klmack]

Hi, very new at trying to use Access SQL, need to compare 2 tables of employees, say "Fac" and "Hr", where the Badge Number is the primary key, and look for mismatched pay rates, but only for the same 2 rows/records that are linked by key. This query showed the two tables linked at Badge Number in Design View, but if it matched a Fac hourly rate anywhere in the Hr column it would not return a difference. I tried adding a CONSTRAINT for PRIMARY KEY and fiddled with DISTINCT ROW and it didn't like my syntax. Could anyone please help me limit this query to just compare the fields in the same record/row? p.s. I don't know how to use Visual Basic either.

SELECT Fac.*, Hr.*
FROM Fac LEFT JOIN Hr ON Fac.[Hourly Rate]=Hr.[Hourly Rate]
WHERE (HR.[Hourly Rate] Is Null);

Thanks much,
Kerry

 

[PHV]

Have you tried this ?
SELECT Fac.*, Hr.*
FROM Fac INNER JOIN Hr ON Fac.[Badge Number] = Hr.[Badge Number]
WHERE Fac.[Hourly Rate] <> Hr.[Hourly Rate]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[klmack]

Thanks PHV it worked perfectly! Much better than the "Is Null" thing...Now I can stop banging my head against the wall and see some results!
Kerry