Cognitive filtering subform from memo field using keywords

[Mark_Hamilton]

Hi All,
I have three tables one with a field called Problem.[issue] which is a long text field (memo), in another other table is an unrelated field with a short text field, Department.[Responsible].
The last table contains a field with keywords and a field with the corresponding responsible department.
I wish to be able to search the Issue field as the user types, looking for keywords to filter an unbound subform displaying the responsible field.

The result in the subform should display various records that match a keyword found within the issue field for example:
In Issue field "The lights are not working in the office"
The subform should return "Maintenance" based on a keyword "Light"

Im looking for ideas as to how to achieve this as my access knowledge is fairly limited.

Many Thanks

 

[Ranman256]

make a continuous form,
in the form header make a text box : txtFind
in the Afterupdate event of the textbox put this code.

sub txtFind_Afterupdate()
const Q = """"

if isNull(txtFind) then
   me.filterOn = false

else
   me.filter = "[keyword] like " & Q & "*" & txtFind & "*" & Q
   me.filterOn = true
end if
end sub

this only works with a single word in the box.
if you want to use many words in the box, it will require more code.

 

[missinglinq]

...if you want to use many words in the box, it will require more code...

Much, much more code, I suspect!

One of the Cardinal rules to having a well designed, normalized database is that no single field will contain more than one individual piece of data...and I know you won't want to hear this...but it appears that you've violated this rule.

Data stored in Memo fields should be restricted to "notes" or narrative type information, such as a salesman's notes on a customers, a doctor's progress notes on patients, or, as in your case, client notes.

Working primarily in a Heath Care environment, I've used Memo fields extensively for a number of years, without ever having problems, corruption or otherwise. I've always attributed this to the fact that I've followed one simple rule:

Never, never, never place data in a Memo field if there any possibility that you will ever need to search, sort, parse or in any other way manipulate the data!

Instead of using a Memo Field (Long Text) you should be using an 'issue' Field in a Subform...with each component listed in a separate Record. And these components should probably be selected from a Combobox, to insure that every user will be using the same words to describe a given problem. Trying to search 'free text' is bound to be, in many instances, a futile task.

Just my opinion, of course...your mileage may vary!

Linq ;0)>

 

[isladogs]

I agree completely with linq
There are tools available which allow search as you type. For example http://allenbrowne.com/AppFindAsUType.html
However these are designed for finding all examples of test starting with the text entered in a specific field (or perhaps a number of fields)
They are NOT designed to find:
a) matching text in the middle of a string then lookup a corresponding match
e.g.

In Issue field "The lights are not working in the office"
The subform should return "Maintenance" based on a keyword "Light"

b) text in a free form memo field though I have done this to a limited extent successfully in the past

To get both a & b to work will probably require a huge amount of code based on fuzzy logic e.g. soundex. If you succeed performance will be sluggish or impossibly slow.

Instead, I suggest you provide users with a series of cascading combo boxes to allow them to get to the desired outcome. There's a good example here: http://www.fmsinc.com/microsoftaccess/forms/combo-boxes/cascading.html

 

[jdraw]

I agree with the previous posters. Use memo fields extremely sparingly and only for general verbiage storage and minimal, if ever, search.
I would also recommend that you review your existing/proposed issues and identify some classification system/taxonomy. You will reduce complexity if you you have some hierarchical issue classification that users can select. This will give you consistency in terminology .
Your initial classification may not be complete, so you could allow for additions that you have vetted and approved through some admin process.
I'm not sure that keywords are department specific--but then I don't know your business nor the issues, their importance/criticality nor frequency.

Once you get your taxonomy/hierarchy set up, you can use cascading combos as others have advised.

It would be helpful to readers and you if you provided some real examples of issues and exactly you want to do with them --search, calculate/process....
Good luck.

 

[MajP]

I disagree that this would be complicated to do. If a person writes a trouble ticket with say 2000 words. I would then simply put each word into an array which can be done simply with the split function. Lets assume there are no key words less than 4 characters. Loop the words and each word greater than 3 in length get inserted into a temp table using a sql insert for efficiency. Then do an inner join of the temp table to the keywords table to return possible departments. My guess that would take a couple of seconds max. Not saying this is the best approach, but definitely doable and not difficult to code. Since you are going to get a lot a duplicates you can either remove the duplicates up front or simply a distinct query on the backside. Not sure what would be the most efficient.
However, my guess is that you could get a workable solution pretty easy, a good solution will be hard, and a very good solution will be very hard.

 

[Mark_Hamilton]

Thankyou everyone for your replies,
I had a bit of a think about how I could do this and although its not entirely accurate, its a dirty way to suggest the Department(s)

The Table 1 provides the keywords and an associated Department. Where there are duplicate keywords for different departments, I added a field to describe the departmental function.

I used a unbound txtbox and created the following event on_update:

Private Sub Text20_AfterUpdate()

strSQL = "SELECT Forms![Form1]![Text20] AS Expr1, Table1.Keyword, Table1.Solution " & _
"FROM Table1 " & _
"WHERE (((InStr([Forms]![Form1].[Text1],[Table1].[Keyword]))>0)); "

Me.List18.RowSource = strSQL
Me.List18.Requery

End Sub

I also created an unbound Listbox to display the results.
When the user clicks on the appropriate department, my form displays the selected departments details including the contact numbers.
Once again, thanks for your help, if you know a cleaner way to do this, Im all ears!