Function that loops through a table looking for keywords


I'm trying to build a fraud detecting query on our FedEx Account.  We have hundreds of shipments each month.  We also have an idea of who the guilty parties are.  What I want to do is create a function that takes one parameter, the field I'm searching.  I want to compare that field against a another table that lists keywords that we are looking for.  I'm assuming I'll have to write some loop that goes through each record in the keywords table then uses the instr (or similar function) to check if that value exists in the field in question.  Does anyone know how I could approach that?  The looping through a table is the part that really stumps me.  If you know of a better way to do it, I'm open to that, as well.
BBluAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
No need for code, actually.  For example:


SELECT s.*
FROM Shipments s INNER JOIN
    Keywords k ON s.Descr Like "*" & k.Keyword & "*"

Open in new window


Be forewarned that that will take a while to run if you have a lot of data.
0
BBluAuthor Commented:

I wasn't even aware of the Keywords [command/statement].  But what if I want to have query that has a binary field ([Fraud?]), that I want to check 'yes' or 'no' depending upon whether any of the keywords found in the keywords table were found in the field in question?  
0
BBluAuthor Commented:
I've attached an example database keywords.accdb
I'd like to search the one field in the Table table for any of the values listed in the keywords table and return a 'yes' or 'no' or true or false.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Patrick MatthewsCommented:
There is no keywords command or statement.

You said you had a table of keywords, so I ginned up an example of how one could join to such a table :)

Can you post an MDB version of your file?
0
BBluAuthor Commented:
Here you go keywords.mdb
0
Patrick MatthewsCommented:
OK, so editing my previous approach to reflect your table/column names:

SELECT t.ID, t.phraseField, k.Field1
FROM [Table] AS t LEFT JOIN 
    keywords AS k ON t.phraseField Like "*" & k.Field1 & "*";

Open in new window


This is Query 1 in the attached database.

That returns your phrasefield and either the keyword it matches or, if there is no match, a Null.

Of course, if your phrase contains >1 keyword, that will return duplicate rows.  For example, consider:

Dogs and cats, living together--mass hysteria!

That matches both dog and cat, and thus would return two rows.

To deal with that, I created Query2:

SELECT t.ID, t.phraseField, DConcat("Field1", "keywords", "'" &  t.phraseField & "' Like '*' & [Field1] & '*'") AS keywords
FROM [Table] AS t;

Open in new window


That relies on the DConcat function from my article http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

The attached database has a copy of the code, which is reproduced in the article and also here:

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
    Optional Limit As Long = 0)
    
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
    
    ' Requires reference to Microsoft DAO library
    
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
    
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, if it is
    '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
    '   column gets sorted
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
    
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
    
    On Error GoTo ErrHandler
    
    ' Initialize to Null
    
    DConcat = Null
    
    ' Build up a query to grab the information needed for the concatenation
    
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")
        
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
    
    Set rs = CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF
            
            ' Initialize variable for this row
            
            ThisItem = ""
            
            ' Concatenate columns on this row
            
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next
            
            ' Trim leading delimiter
            
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            
            ' Concatenate row result to function return value
            
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
    
    ' Trim leading delimiter
    
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    
    GoTo Cleanup

ErrHandler:
    
    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
    
    DConcat = CVErr(Err.Number)
    
Cleanup:
    Set rs = Nothing
    
End Function

Open in new window

Q-26936034.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JezWaltersCommented:
I might be missing something, but can't you just avoid duplicates like this?

    SELECT DISTINCT [Table].*
    FROM [Table], [keywords]
    WHERE InStr([Table].[phraseField], [keywords].[Field1])
0
JezWaltersCommented:
Or, if you prefer:

    SELECT DISTINCT [Table].*, InStr([Table].[phraseField], [keywords].[Field1]) <> 0 AS [Fraud]
    FROM [Table] LEFT JOIN [keywords]
    ON InStr([Table].[phraseField], [keywords].[Field1])
0
Patrick MatthewsCommented:
JezWalters,

Depends on what you want to return.  If you want your results to also indicate which keyword(s) applied, then your DISTINCT approach will not do.

My suggestions in my last post indicate which keyword(s) applied, if any, and so any attempt to use DISTINCT would not have suppressed the additional rows.

OTOH, if you do not need or want that information, then your approach will do quite nicely.

:)

Patrick
0
BBluAuthor Commented:
Works Perfectly.  Sorry for taking so long to accept, but I like to (at least try to) figure out why/how something works so I can ask follow-up questions while the topic is fresh.  I'm going to need some time to digest this one, so I'll close it out.  Thank you both very, very much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.