Question Categorizing Transactions, looking for ideas

[AUGuy]

What I'm trying to think out is a way to take a list of bank card transactions and categorizing them from the transaction description provided by the bank.

The way I envision this working would be to have a table to hold a list of description keywords. These keywords (such as "McDonalds") would be compared to the transaction data description. Since the bank description varys by location, transaction type, etc... I'd like for it to find transactions that are similar to the keyword. I.E. "MTGM ACH 033011 MCDONALDS" bank trxn description would be identified by comparison to the key in the table of "McDonalds".

Any thoughts on how to do this, or if there is a better way to identify and categorize these types of transactions?

Thanks,
G

 

[AUGuy]

I did a little bit of reading in a reference book and found a handy object to use: InStr
Below is the code i came up with to iterate through the transactions in the table, identify and set the category based on a description definition table, and move to the next transaction.

Dim dbDatabase As Object
Dim rstDescrList As Object
Dim rstTransList As Object
Dim strTransCat As String
Dim strKeyWrd As String
Dim strTransDescr As String
Set dbDatabase = CurrentDb
Set rstDescrList = dbDatabase.OpenRecordset("tblDescrList")
Set rstTransList = dbDatabase.OpenRecordset("tblTransactionData")
 
 
Do Until rstTransList.EOF = True
strTransDescr = rstTransList("Description").Value
    Do Until rstDescrList.EOF = True
        strKeyWrd = rstDescrList("Description").Value
        strTransCat = rstDescrList("Category").Value
            If InStr(1, strTransDescr, strKeyWrd, vbTextCompare) Then
                rstTransList.Edit
                rstTransList("Category").Value = strTransCat
                rstTransList.Update
                Exit Do
            End If
            rstDescrList.MoveNext
        Loop
    rstTransList.MoveNext
    rstDescrList.MoveFirst
Loop
End Function

Maybe this could be useful to someone else.

Feel free to comment with changes if something could be done better.
Cheers,
G