Parsing through a string and counting number of occurence

[akira]

Hi all,

Running into a problem here. I have about 150k rows that look like this

SHORT_DESCRIPTION
unable to view
unable to send out some stuff


what is going on

Is it possible for access to query through each field, and tell me the total number of occurence for each word? In this small example, the output would be:

Unable 2
to 2
view 1
send 1
out 1
some 1
stuff 1
what 1
is 1
going 1
on 1

Is this possible?

Thanks in advance!

[orange]

Can you tell us more about the issue/opportunity you are facing?
Why would a count of the strings be useful? We really can't respond more precisely until we understand the issue/rationale.

[akira]

Thanks for your reply orange.

I have a table called PROBLEM_Tbl, that stores ticket information for each customer that called in (we are a service desk). So for example,

ID#0001 | Customer Name | SHORT_DESCRIPTION| START_TIME | RESOLVED_TIME | etc...

I am trying to parse through the short_description of all the tickets that are available, and see which word appears the most often. This allows me to narrow our strategy to focus on specific issues. For example, if the word "Calendar" appears 40% of the time for MS Outlook issues, then I know theres something to work on for MS outlook Calendar problem.

[orange]

Just thinking as I type:
Do you have some Category with values suchas OutLook, Access or whatever you deal with?

I think if, when you record a problem/issue/ticket, you had some lists from which to select a keyword, you may reduce the size of the problem.
But then again, I'm only thinking while typing. Perhaps you could tell us more about your "service desk", your clients and their problems generally.

[akira]

Yes there is such field called KEY_ITEM (WINDOWS 7, WINDOWS XP, MS OUTLOOK etc..). However that is the lowest level we have in our system which really doesn't tell us much what the underlying issue is. All we can tell is, we have x number of tickets for windows 7, xp, outlook blah blah... In order to see what is the root cause of the ms outlook issue, we have to look into the short description to figure out "oh it's a calendar issue".

Is there a way to parse through the description and summarized the number of occurence for each word?

[orange]

Do you have some sample data you can post?

If you do some analysis on keywords below your major subjects, then create a form to allow
-choosing the MAIN Category for the issue/ticket
-then ability to multiselect other words/terms,that fall beneath the Main topic.

You probably will still need some free text based on the user's complaint/requirement.
our Main topics seems to be Operating System, Outlook

Outlook
- forms
- vba
- rules

[qa12dx]

i'm sure there is a better way, probably ask someone who knows SAP. SAP deals with such issues well.

scratched my noggin a bit and here is what i came up with. hope u r good at automation.

- break each line up as a txt file
- import using delimited, space being the marker. so each word becomes a field
- append into a table with as many field as u think will be words.
- for each field do a qry, 1st collumn - groupby, 2nd coll - count
- now append all these qrys into a table with 2 fields, text (stores the word) and number (stores the count)
- run a qry on this table, text field - groupby, number field - sum

that will give u the counts even if a word is repeated in the same sentence

seems archaic i know, but thats the best i could come up with