MS Access keyword search of data table text field using separate keyword table

Hi,

I am doing some analysis on a data extract, to identify reasons for call based on the incident cases notes. Unfortunately, I'm not familiar with MS Access so I'm looking for some help.

I am trying to build a keyword search, but there are a lot of unknowns and I expect this to be an iterative process to arrive at the correct words to be an effective search. Given the (lack of) integrity of the data I want to try and stick to broader categories rather than specific words - e.g. 'screen', 'display', 'image', 'picture' could all relate to 'visual issues'.

What I would like to do is to hold the individual keywords in a separate table, which would then be used as the basis for searching the text field in the data table. That way I can keep refining the actual words without needing to delve back into the query design / sql.

To complicate matters slightly, there are actually two data tables, one with the product references and the other with the actual case notes.

I have got as far as searching the text field for a specific word, but I am stumped as to how  to get this to poll the keyword table for words, and ideally make this more of a fuzzy lookup approach.

I'm not sure how useful this will be but here is the SQL code:

SELECT [RN Product].[Product Level 2], [RN Product].[Product Level 3], [RN Product].Reference, [RightNow Note Data].Text, IIf(InStr(1,[Text],"picture")>0,1,"") AS Picture
FROM [RightNow Note Data] INNER JOIN [RN Product] ON [RightNow Note Data].[reference ] = [RN Product].Reference
GROUP BY [RN Product].[Product Level 2], [RN Product].[Product Level 3], [RN Product].Reference, [RightNow Note Data].Text, [RN Product].[Top Level], [RN Product].[Product Level 1], IIf(InStr(1,[Text],"picture")>0,1,"")
HAVING ((([RN Product].[Product Level 2])="Television") AND (([RN Product].[Product Level 3])="something"));

Open in new window

pendulumAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
You might want to start by reading my article on Complex Text Filters.  It contains some good ideas and a sample database.
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I'm not familiar with MS Access so I'm looking for some help.>
How much help?
This may be akin to saying :  "I'm not familiar with Cars so I'm looking for some help building a transmission"
;-)

fyed's article will be a big help here.
The key word there is "Complex"
If you are new to Access you may have a substantial learning curve here.
The real issue with any "Search" system is creating the "User Interface"
But I am sure fyed can assist.

<and ideally make this more of a fuzzy lookup approach.>
This is an entirely different subject...
But you can see the solution here for more info:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22757244.html

JeffCoachman
0
 
pendulumAuthor Commented:
Complex is the operative word, but a really useful approach for me to integrate. Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.