How to create and use a filter table / query in Access

[KevinTekTip]

I have a 1 column table that is filled with unique records [named "tbl_Filter"]. For instance, these are the values: record1, record2, record3... all unique.


I have another table that is filled with hand-entered records [named "tbl_HandEntered"]. The hand-entered records in this table are not all unique, but are supposed to be limited to the values that are listed in the "tbl_Filter". For instance, these are the values: record1, record1, record2, record3, record3


I am trying to create a query to identify the records in "tbl_HandEntered" that are misspelled, blank, or simply contain values that are not acceptable. The only acceptable values in "tbl_HandEntered" are the values that are listed in "tbl_Filter"


I am doing this in MS Access so if I am wondering if I should incorporate VBA into this filter or if I can do this 100% through a SQL query.


Thank you for your advice and suggestions!

 

[fneily]

I have a 1 column table that is filled with unique records [named "tbl_Filter"]. For instance, these are the values: record1, record2, record3... all unique." Is it me or does this statement doesn't make sense? A column holds a single type of data, not a record.
Have you tried the Find Unmatch Query Wizard? Make the table with the typos your first choice in the Wizard. The recordset will show all the bad typos.
More importantly, why are users typing in the info? Why don't you have a dropdown with all the available choices based on the table with the correct spellings? That's the purpose of dropdowns. The less typing, the less errors.

 

[lespaul]

SELECT Distinct FieldYouAreInteresetedIn FROM tbl_HandEntered WHERE FieldYouAreInteresetedIn NOT IN (SELECT DISTINCT FieldInOtherTable FROM tbl_Filter)

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com