I have two lists, A and B. List A is a field in a table and contains some items that do not appear in list B.
List B has been compiled using approved data sources and is the "Standard" by which list A will ultimately be corrected.
I need a select query that returns only those items in list A that are NOT in list B. I.e. those items that require correction.
Why do I need such a thing?
The wanted items are either typos or otherwise incorrectly entered data that need to be corrected in list A.
Before we get into normalisation etc, this is an inherited database where EVERY field was text and there were no checks and balances against what was being entered.
This resulted in a horrendous mess that has been largely repaired and is now fit for purpose. There is, however, a small number of errors that are proving difficult to find, hence the two lists.
Normalisation and setting up relationships is in the grand plan but is still some ways off. Getting the existing data corrected has to be my first priority as the database is in daily use and "normalising" faulty data would only exacerbate the problems I already face.
I have tried using various combinations of "is null" and "is not null" on list B. These return nothing or the full list B.
Putting the criteria "is null [listB]!{data] against list A results in an vague error message about operators without operands.
Any suggestions greatly appreciated
List B has been compiled using approved data sources and is the "Standard" by which list A will ultimately be corrected.
I need a select query that returns only those items in list A that are NOT in list B. I.e. those items that require correction.
Why do I need such a thing?
The wanted items are either typos or otherwise incorrectly entered data that need to be corrected in list A.
Before we get into normalisation etc, this is an inherited database where EVERY field was text and there were no checks and balances against what was being entered.
This resulted in a horrendous mess that has been largely repaired and is now fit for purpose. There is, however, a small number of errors that are proving difficult to find, hence the two lists.
Normalisation and setting up relationships is in the grand plan but is still some ways off. Getting the existing data corrected has to be my first priority as the database is in daily use and "normalising" faulty data would only exacerbate the problems I already face.
I have tried using various combinations of "is null" and "is not null" on list B. These return nothing or the full list B.
Putting the criteria "is null [listB]!{data] against list A results in an vague error message about operators without operands.
Any suggestions greatly appreciated