Word Counter (v2000)

Topic

New Reply

I have a memo field capturing notes from dream study patients. We want to analyze the memo field in each record and produce a result that counts the number of occurrences of every single word for all records combined. I know ‘a’, ‘and’, ‘the’, etc. will appear a lot but we can exclude them from the final product. My challenge is how to count the total number of times each word appears.
Hmmmm???
Thanks!

Reply | Quote

Replies

You need a words table, with two fields word and count.

You could then use DAO code to loop through each of the records in your main table, parse the memo field into words, one word at a time and check the words table for this word. If it is present increment the count. If not add the new word to the table and set the counter to one.

post 488688 had some code that is broadly very similar, and could be modified to do the job you want.

Reply | Quote

I have attached a small demo. The module basParse contains a procedure WordFrequencies that takes two arguments: the name of the table and the name of the field whose word frequencies you want to count.

The code assumes that you have created a table tblWords with 2 fields: Word (text, size large enough to hold the longest word you expect to occur, 30 should be ample), and Frequency (number). Existing records will be removed at the beginning of the procedure. The code uses the VBScript regular expressions object to parse the field into individual words (defined as consisting of upper and lower case letters, digits and underscores).

The code uses DAO recordsets, so you need a reference to the Microsoft DAO 3.6 Object Library; if you prefer, you can easily adapt the code to use ADO instead of DAO.

The first procedure in the module, TestParse, runs WordFrequencies on the tblEmployees table (a stripped down version of the table in the Northwind database that comes with Access).

Reply | Quote

Hi Hans

Nice demo, don’t need it today, but who knows about tomorrow.

Thanks, John

Reply | Quote

Worked perfectly!!! Thanks Hans for sharing your expertise!

Reply | Quote