Count number of time a specific word appears in a column

  • Thread starter Thread starter Colin
  • Start date Start date
C

Colin

I have a small HelpDesk system that stores the agents answers (fldA) in a
column. It's easy enough to find out how many cases have been created, but
I'd like to know how many times an agent has touched the case. For example,
the customer complains their product doesn't work. The agent would reply
asking for more info. Every reply is appended into the same row in the
column fldA. Everytime it's appended the contents of the message contain
the words "BY AGENT". How do I count how many times a specific word ("BY
AGENT") appears in a column?

Thanks,
Colin
 
If you were to re-structure and normalize your tables, this would be an easy
sql statement. Is there any chance that you could create a related table of
agent responses?
 
Dear Colin:

If the software automatically adds to the length of a column, there is a
good chance it will grow too large to fit in the maximum size for that
datatype. It would be much better to store each "case" in a separate table
using the PK of the row to which it is related. This would also make the
search you want easier.

For now, I recommend you write a function returning an integer that can
count this for you. You'll be able to use that in any query where you need
this value.

Tom Ellison
 
Sorry, I need to explain it clearer. The software does create a new caseID
for each case, but sometimes it can take 3 or four posts(emails) to solve
the customers problem. The agents reply is appended to what was already
there.

I want to find out how efficient our agents are by seeing how many posts it
takes the agents to solve an issue. The only way to do this is by counting
the text "BY AGENT" in the column since it is the first thing appended to
the column data.

So how do I count the number of occurences of a word in a column?
 
Dear Colin:

I'll assume you have some way of guaranteeing the text will never grow too
large to fit in the column. This is still a bad design, however, as you may
eventually experience.

As I said before, you need to code a Public VBA function to do the counting.
Use InStr to find the first occurrance of BY AGENT, then move forward to a
point past that and repeat until no more are found, counting as you go.
Return this count.

Tom Ellison
 
Sneaky way to do this is to use the replace function along with some
division. For example to count the number of "order" in
"Order Order Sentence Order"

(LEN("Order Order Sentence Order") - Len(Replace("Order Order Sentence
Order","Order","",1,-1,1))) / Len("Order")

That assumes that the replace function will work in your version of Access
2000 or later. And you should do this in a function in Access 2000 since
the replace function is in VBA, but not always available through the
expression service.
 
Back
Top