How to Filter and/or Compare Data Against a Table
Posted on 2012-08-11
I am developing a leads management database for a client in MS Access 2010. We will be collecting leads from various sources and importing into MS Access. I would like to have a table called tblFilter that has single entry terms in it that we want to compare against the leads collected in order to filter out the spam leads, etc.
for example tblFilter may have entries in it like
so if a First Name in tblNewLeads is like, or equal to Mickey or Last Name is equal to or like Mouse - (or any other term entered in tblFilter) I want to flag that record as "Filtered" so we can look at it and determine if it is spam or not
Another example may be Address - if Address is equal to or like xxx - then i want to flag that record, or if Address is equal to or like yyyyyyyyy, or zzzzzz, etc I want to flag that record
same for email - if email is equal to or like Mickey - then flag
Imagine 1000 leads, and the database Filtering out the spam and setting a flag, like a yes/no field named "Filtered"
We know we will have to do some manual work, but if i can automate part of it, I will be happy.
I am wondering what the best approach to take is to accomplish this. i know it will not work perfectly, but in time we can add more entries to tblFilter as we see what is in the leads collected and it will get a bit more accurate.
My thoughts are to loop thru each record, and use an IN statement??? and if it is true, then update Filtered to True
i can do this once for each field I want to filter, basically FisrtName, LastName, Address, City and EMail - there are validations to force a user to select a state when filling out the form, but they still can enter anything for name, address, city
I will have a tab on a main menu showing Filtered records so we can look at them periodically and determine if we want to delete, or override the automated process.
Any ideas or examples is appreciated.