I have two tables in Access, one is essentially a customer record with their personal info and another is full of valid values, for example state abbreviation and gender. I am trying tocompare the two tables so that it checks the gender and the state codes against whats in the reference table and pull the customer ID (another field in the first table) and report that theres an issue by putting the customer id and the values im using to validate, in another table. B ut instead of only returning values that dont match it returns every record from the first table. so its not really verifying anything so much as just copying and pasting, instead of only returning values with no match in the second table.
My code looks like this
DoCmd.RunSQL
"INSERT INTO Issues
SELECT Eligibility.ID, Eligibility.[Member Id], Eligibility.[Sex Code], Eligibility.State
FROM Eligibility
LEFT JOIN Ref ON
(Eligibility.[Sex Code]<>Ref.[Sex Code]) AND (Eligibility.State<>Ref.State);"
What you will want to do, is actually change the JOIN
clause to match the Sex Code
and State
, but only return the rows that do NOT match.
INSERT INTO Issues
SELECT Eligibility.ID, Eligibility.[Member Id], Eligibility.[Sex Code], Eligibility.State
FROM Eligibility
LEFT JOIN Ref ON
(Eligibility.[Sex Code]=Ref.[Sex Code]) AND (Eligibility.State=Ref.State)
WHERE Ref.[Sex Code] IS NULL
The WHERE Ref.[Sex Code] IS NULL
will filter out the valid rows, and only return the rows that do not meet your JOIN
criteria.