Learn how to a build a cloud-first strategyRegister Now


Allowable text phrases

Posted on 2011-05-10
Medium Priority
Last Modified: 2012-05-11

I am working with a large table with Federal Express and American Express transactions.  I am trying to group the transactions based on a few of the fields.  And based on what's in there I want to create a Category item/field.  For example, the new field is Category.  If "ship" or "ABC Co." or "XYZ" exists inside the "Shipper" field, I'd like to assign "Cost of Goods Sold", if "Expedia" or "Travelocity", I want it to say "Travel".  I'd like to have a table somewhere with two columns, one for search text and one for assignToCategory.  Then, in this query, I'd search the field on each record through this other table and return the value.  I'd like to make it easy on myself and just be able to add values to the table.  Does anyone know how I could approach this?
Question by:BBlu
  • 5
  • 2
LVL 12

Expert Comment

ID: 35734323
You can write a custom Function for this;

Public Function fCategory(Phrase As String) As String

If IsNull(Phrase) Then
Category = Null

Select Case Phrase
Case "ship" Or "ABC Co." Or "XYZ" Or "Shipper"
fCategory = "Cost of Goods Sold"
Case "Expedia" Or "Travelocity"
fCategory = "Travel"
... etc.
End Select

End Function

Place the function into a new Module in your database and Save it.

Then you can use the function fCategory([YourSearchFieldName]) in your Form/Query/Report.


Author Comment

ID: 35734337
That's kind of what I was going to do, but thought there might be an easier way via an independent table through which each record ran to check.
LVL 12

Expert Comment

ID: 35734344
You can offcourse do this in a Table, something like:

PhraseID           AutoNum
Phrase              Text
CategoryID        Number

CategoryID        AutoNum
Category           Text

Link the Category table to tblPhrases... That would work.
Enter each Phrase and their Category.

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


Author Comment

ID: 35735123
Hey Daniel-

But the phrases won't be exact matches.  Now that I look at it, I don't think the first Select Case would work either.  I'm looking in the Description field for "ABC Co." anywhere in the field.  Any ideas on how to address that?

Author Comment

ID: 35735319
I think this might work but I don't know how to do it.

1.  I have my main table with the Description fields and records I'm trying to categorize.
2.  I create another table (CategoryTable) that lists the different keywords that might appear in the Description field and, if found, what category that should be assigned to.
3.  I create a function with 1 argument, the Description field
4.  With each record in the main table, I loop through the Category table.
5.  With each record in the Category Table, I check instr([Description],[phraseToLookFor]) and if the value >0, we assign the Category to that field, if not we move to the next record in the CategoryTable.

I've attached some code that I've worked on after I found some info. via Google.  It's way off and doesn't work, but it gives an idea of what I think might work.

Sub getCategory()

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim sValue As String

sSQL = "SELECT * FROM T_CategoryAssignment"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

DoCmd.SetWarnings False

Do Until rs.EOF
sValue = rs("Description").Value
MsgBox (sValue)
'DoCmd.RunSQL sSQL

DoCmd.SetWarnings True

End Sub

Open in new window

LVL 44

Expert Comment

ID: 35743345
It would help if you would provide the table name rather than "I am working with a large table..."  - same goes for the field names.

Accepted Solution

BBlu earned 0 total points
ID: 35751007
Here's what I came up with after getting a lot of help.  The fields and tables are listed in the code.  I'm open to hearing how others would do it before I close it with my solution, especially since my thoughts were to do it with a function.
Private Sub Process_Click()
    DoCmd.SetWarnings False
    'Dim stDocName As String
    Dim DBS As Database
    Dim qdfRB, qdfTree As QueryDef
    Dim DataRecsRB As Recordset
    Dim StrSqlRB, StrSqlTree As String
    Set DBS = CurrentDb
    StrSqlRB = " SELECT T_CategoryAssign.ID, T_CategoryAssign.KeyPhrase, T_CategoryAssign.Category " _
             & " FROM T_CategoryAssign " _
             & " ORDER BY T_CategoryAssign.ID;"
    Set qdfRB = DBS.CreateQueryDef("", StrSqlRB)
    Set DataRecsRB = qdfRB.OpenRecordset()
    If DataRecsRB.RecordCount > 0 Then
               Do Until DataRecsRB.EOF
                    If Not IsNull(DataRecsRB![KeyPhrase]) Then
                            ' UPDATE INFORMATION - T_AmexFedexTxns.[Category]
                            DoCmd.RunSQL " Update T_AmexFedexTxns Set T_AmexFedexTxns.[Category] = """ & DataRecsRB![Category] & """" _
                                       & " WHERE T_AmexFedexTxns.[Recipient Company] Like  """ & "*" & DataRecsRB![KeyPhrase] & "*" & """ ;"
                    End If
    End If
End Sub

Open in new window


Author Closing Comment

ID: 35951066
No one had any alternatives to the ones I provided.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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…

810 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