Query to find keywords in description field and give count

[mhshane]

Hello. Hope you are doing well.

Trying to analyze helpdesk tickets based on keywords such as "install"
I want to get a count of how many tickets involve user needing to install something

Example:
Keyword: install

Need to count the records where "install" is in the description but not "uninstall"

Table 1: list of keywords
Single column,
Header = "Keyword"

Table 2: all service desk tickets for last 3 months
Headers =
Ticket number
Date
User
Description - this is where the keyword will show up


Instr does not do exact. For "install it is also catching "uninstall" and "reinstall"

Not sure how to get a query built to produce the following two column idea:
Keyword - Count
"Install" - # of tickets
"Uninstall" - # of tickets
"Email not working" - # of tickets
"Admin rights" - # of tickets

Keywords table doesn't tie to the Tickets table so I am stumped.

This is as far I get:
Expr1: Instr(1,[Description],[Keyword],1)

Ticket example:
"My Adobe Acrobat software will not install. Says I need admin rights. Please help."

Phase 1: rudimentary count
Install would get a count of 1
Admin rights would get a count of 1

At some point if I get this working then I'd look to give "Admin rights" more weight than "install".
So Install would = 0
Admin rights = 1

Hope that makes sense.
Thanks.


MS Access 2016

 

[dhookom]

I think you would want to use a criteria of

Like " " & [Keyword] & " " or Like " " & [Keyword] & "."


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Do you mean:[tt]
Like "[highlight #FCE94F]*[/highlight] " & [Keyword] & " [highlight #FCE94F]*[/highlight]" or Like "[highlight #FCE94F]*[/highlight] " & [Keyword] & "."[/tt]

That would depend if there are any standards in Description.
Keyword: install <- the above would work fine, but
Install: Keyword <- would not

Ooops! I just realized... I saw your "Keyword' as "Keyboard"
Some samples of your Description field's data would be very helpful.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

Thanks Andy!

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

If that is your
Description field data:
[blue]Install[/blue] drivers
MS Office [blue]install[/blue] on computer XYZ123
Mouse [blue]install[/blue]
Trackball [blue]install[/blue].

So, can either have [blue]install[/blue] at (1) the beginning and a Space, (2) in the middle, Space on both sides (3) at the end without the period, or (4) at the end with the period.

[pre]...
WHERE UCase(Description) LIKE " & UCase([Keyword]) & " *"
OR UCase(Description) LIKE "* " & UCase([Keyword]) & " *"
OR UCase(Description) LIKE "* " & UCase([Keyword])
OR UCase(Description) LIKE "* " & UCase([Keyword]) & "."[/pre]

UCase is just for a good measure...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson