How to Filter and/or Compare Data Against a Table

Posted on 2012-08-11
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


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.


Question by:abinboston
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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 Henry
    Big John
    ( 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.


    Author Comment

    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

    LVL 39

    Expert Comment

    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:
    And almost on every spam filtering software forum you can find many ready samples. Here, for example:
    LVL 30

    Expert Comment

    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.
    LVL 74

    Accepted Solution

    <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)

    Author Closing Comment

    Nice simple process - even simpler than what I got working. Thanks!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now