asked on

Allowable text phrases

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?

danishani

You can write a custom Function for this;

Public Function fCategory(Phrase As String) As String

If IsNull(Phrase) Then
Category = Null
Else

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.

HTH,
Daniel

BBlu

ASKER

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.

danishani

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

tblPhrases
PhraseID AutoNum
Phrase Text
CategoryID Number

tblCategory
CategoryID AutoNum
Category Text

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

HTH,
Daniel

BBlu

ASKER

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?

BBlu

ASKER

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
rs.MoveFirst

Do Until rs.EOF
sValue = rs("Description").Value
MsgBox (sValue)
'sSQL = "INSERT INTO TEST_DOC SELECT * FROM " & sValue
'DoCmd.RunSQL sSQL

rs.MoveNext
Loop
DoCmd.SetWarnings True
rs.Close

End Sub

Open in new window

GRayL

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.

ASKER CERTIFIED SOLUTION

BBlu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BBlu

ASKER

No one had any alternatives to the ones I provided.