Identify frequent words and phrases in a field (1 Viewer)

Hello,

I have a database in Access 2007 which contains a field called PO Line Item data. I am looking to identify which are the most frequent words and phrases (perhaps up to 4 or 5 words in length) as well as a frequency count. I would like access to create another table to record this information.

How can I write this query? I have only intermediate knowledge of access and none in VBA.

Thanks for your help.

Welcome aboard
You can't do this with a query. You'll need to use VBA. Open a recordset, for each record use the Split() function to create an array of words. Loop through the array and write a record for each element. Clear the array, read the next record and loop back to the top.

Once the table of words is built, you can use a query to count the occurances for each word.

Hello Pat,

Thanks for replying. I'm not sure where to start with VBA, especially if the VBA code cannot be called from a query.

Hello again Pat,


I found this code below which has helped. I can run through each word and add this to a table. I will then need to work out how I can grab 1 or 2 words from the keyword I have identified to make up a 3 word phrase.

Thanks again.

Function SelectWords(InString As String, InCount As Integer) As String
'-- Return a string with InCount words from the InString supplied separated by one space as a delimiter
Dim MyArray() As String
Dim x As Integer
SelectWords = "" '-- Initialize the return value
If Len(InString) > 0 Then
MyArray = Split(InString, " ")
If InCount > UBound(MyArray) Then
'-- Limit the InCount to Max UBound of the Array
InCount = UBound(MyArray)
End If
If (UBound(MyArray) > 0) And (InCount > 0) Then '-- We have some array elements
For x = 0 To InCount - 1
SelectWords = Trim(MyArray(x)) & " "
Next
'-- Strip off the last space
SelectWords = Left(SelectWords, Len(SelectWords) - 1)
End If
End If
End Function

Unfortunately I don't know how to get 2 or 3 word phrases. Does anyone know how I could do this in SQL?

out of interest, where does this datra come from

ideally this po line description would be in a "products" table, and all you would store in the order (orderline) table would be the product id.

analysing the product file is a different thing. you could add any number of attribute fields to determine the type of product you have.

this sort of approach is bound to be better than a textual analysis, surely.

You would need to pre-define the multi-word phrases in a separate table. Then as you extract the single words from each row, execute a second loop to see if the whole string contains any of the multi-word phrases from the new table.

Hello again,

I did a google search on "text analysis with Microsoft Access" and found a great Access database which has solved my problem. Please see:



funsci.com/fun3_en/lexicon/handbook.htm



for a downloadable database and description of how this works.

Thanks to all for your advice. For me this post is solved.

Thanks. That looks very interesting.