[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to Filter and/or Compare Data Against a Table

Posted on 2012-08-11
6
Medium Priority
?
633 Views
Last Modified: 2012-08-19
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

Mickey
Mouse
xxx
Donald
Duck

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.


Thanks

AB
0
Comment
Question by:abinboston
6 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38284651
1. I will presume that the "Flag" will be set for either condition being true.

2. You also need to be more detailed on your exact criteria here because this becomes more complex if you want different criteria in different situations.

3. Note that "Is Like" can also be made to encompass "Is Equal To"
For example: Like *john*
...will return:
John
Johnny
John Henry
Big John
st1Q3DGtaerJohnJhayygef4fsv
(...in other words, anything that contains "john")
 

Also, note that detecting "Spam" is something that no program can do with 100% accuracy.
Spanners can access *hundreds* or domains and/or names, so they can change where (and form who) the email is sent from *daily*.  So you may end up having to update your "filter" list perhaps a dozen times per week and end up with thousands of entries which will gradually slow down this "Detect Spam" system.


JeffCoachman
0
 

Author Comment

by:abinboston
ID: 38284700
Thanks for the comment. Like is the criteria we wish to use ....  
Like *xxxxx* is what I am looking to trap

I would not use a word like john in the mix
I am lookin for zzzzzz, xxxxxx and blatant invalid entries

I know it won't be 100% accurate but I still need to know how to do it since it us a requirement of the project

AB
0
 
LVL 40

Expert Comment

by:als315
ID: 38284842
Usually spam filters are using regex. You can find good samples in a Patrick Matthews article.
Here you can find very good tool for building regexes:
http://www.ultrapico.com/ExpressoDownload.htm
And almost on every spam filtering software forum you can find many ready samples. Here, for example:
http://www.vamsoft.com/forum/show/ORF-Technical-Support/909
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 31

Expert Comment

by:hnasr
ID: 38287196
Too many requirements.
I suggest to concentrate on one part of the issue, and when answered open another thread for the next part.
Finally you can join them. You may start a new question for help in integrating all parts.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 38288189
<Like is the criteria we wish to use ....  
Like *xxxxx* is what I am looking to trap
I would not use a word like john in the mix
I am lookin for zzzzzz, xxxxxx and blatant invalid entries
>
Yes, but it would require the same logic.

Try this (sample attached)
Access-EEQ27825776FlagMatchingVa.mdb
0
 

Author Closing Comment

by:abinboston
ID: 38309848
Nice simple process - even simpler than what I got working. Thanks!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question