Extract Multiple Keywords (1 Viewer)

kmrkmj

New member
Local time
Today, 08:32
Joined
Nov 2, 2006
Messages
5
Hello all. Just looking for some guidance with a query. I have one query that finds a keyword in a string based on keywords in a table.

Code:
SELECT 
	excelRaw.rawIncdNum, excelRaw.rawFileName, excelRaw.rawSynopsis, tblClass.class_keyword, tblClass.class_value, InStr(1,[excelRaw].[rawSynopsis],[tblClass].[class_keyword]) AS Expr1
FROM excelRaw, tblClass
WHERE (((InStr(1,[excelRaw].[rawSynopsis],[tblClass].[class_keyword]))>0));


Then my next query will use the first query and display the keyword based on the query results. The string can be any length and there isn’t a naming convention.

Code:
SELECT 
	excelRaw.rawIncdNum, excelRaw.rawFileName, excelRaw.rawSynopsis, qryClassInStr.class_keyword, qryClassInStr.class_value, excelRaw.rawClassRoll
FROM excelRaw LEFT JOIN qryClassInStr ON excelRaw.rawIncdNum = qryClassInStr.rawIncdNum
ORDER BY excelRaw.rawClassRoll;


I’m trying to find a way to search for multiple keywords in the string and then concatenate the results within a column. Any and all help will be very appreciated.
 

isladogs

MVP / VIP
Local time
Today, 12:32
Joined
Jan 14, 2017
Messages
18,186
Hello and belated welcome to the forum.
Your first post was 13 years after you joined which may be a record!
It was also moderated - now approved.

Your first query has no joins between the 2 tables and is what is known as a cartesian join.
It can lead to huge numbers of records and is usually not an appropriate choice

As regards your question, it would help if you gave an example of the data and the type of output you require
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:32
Joined
Feb 28, 2001
Messages
26,996
The structure implied in your first query is a linear list of keywords (for INSTR to have any sort of meaning here). That's gonna be slow and awkward. Instead, make your keyword table vertical - a table with records each containing exactly one of your keywords and its associated qualifiers. Then you can do something like this:

Code:
SELECT excelRaw.rawIncdNum, excelRaw.rawFileName, excelRaw.rawSynopsis, tblClass.class_keyword, tblClass.class_value
FROM excelRaw INNER JOIN tblClass ON excelRaw.RawSynopsis = tblClass.Class_Keyword ;

You don't need Expr1 to tell you which column you matched because in the variant of the table I propose, you never have more than one value in that keyword column. But you have lots of rows, one for each keyword.

What you were doing searches a longer string to see if the keyword matched any element of the string. What I offer is the idea to search a list of strings (via the INNER JOIN), which Access can do very efficiently. Your comparison was horizontal and could in theory run afoul of field size issues. My comparison was vertical and you can have as many keywords as you want, one per keyword record.
 

kmrkmj

New member
Local time
Today, 08:32
Joined
Nov 2, 2006
Messages
5
Thanks isladogs, I like setting records!

I’ll cut out the unnecessary fields at the moment. My tblClass consists of 3 fields.
class_classID, auto number/pk
class_keyword, short text – Contains the keyword value that is being searched for. Can be multiple words
class_value, short text – contains the value I wish to display

I just changed this a bit because the solution The Doc Man gave me will not search a Memo field but does work on a short text field. Also it only finds exact matches. But I can use this solution with something else so thanks!


Code:
SELECT 
excelRaw.rawIncdNum, excelRaw.rawFileName, tblClass.class_keyword, tblClass.class_value
FROM excelRaw, tblClass
WHERE (((InStr([excelRaw].[rawFileName],[tblClass].[class_keyword]))>0));

Code:
SELECT 
	excelRaw.rawIncdNum, excelRaw.rawFileName, qryClassInStr.class_keyword
FROM excelRaw LEFT JOIN qryClassInStr ON excelRaw.rawIncdNum = qryClassInStr.rawIncdNum;

The field I want to search in the [excelRaw].[rawFileName] which is a short text field that can contain more than one word. Think of it as a title to an incident. What was able to do with my examples is compare the [excelRaw].[rawFileName] against [tblClass].[class_keywords] to find a match. My problem is that it stops once it finds a match but there may be multiple keywords contained within [rawFileName]. I’d like a column in the query to display if it finds multiple keywords as keyword1, keyword2, keyword3…

I hope that makes sense. Thanks again!
 

isladogs

MVP / VIP
Local time
Today, 12:32
Joined
Jan 14, 2017
Messages
18,186
Last post also moderated. Posting this to trigger email notifications

Memo (long text) fields cannot be searched using any query
For text (short text) fields, you can use wildcards to search for partial matches.
There are several examples of search forms on Allen Browne's website

You are still using a cartesian or no-join query. Do you have any common fields in both tables that aren't memo fields?
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 08:32
Joined
Jan 23, 2006
Messages
15,361
kmrkmj,

I suggest you show us a few records of sample data.
And an example of the keywords found in that data.
I'm sure there are options.
 

kmrkmj

New member
Local time
Today, 08:32
Joined
Nov 2, 2006
Messages
5
I've attached a mock-up of the database i'm working on. Thanks!
 

Attachments

  • DAR.accdb
    504 KB · Views: 86

Users who are viewing this thread

Top Bottom