Finding a field of keywords in a long text field

%3CLINGO-SUB%20id%3D%22lingo-sub-2894894%22%20slang%3D%22en-US%22%3EFinding%20a%20field%20of%20keywords%20in%20a%20long%20text%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2894894%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20thousands%20of%20keywords%20in%20one%20field.%26nbsp%3B%20On%20another%20table%2C%20I%20have%20a%20field%20listing%20thousands%20of%20long%20text%20records.%26nbsp%3B%20Ultimately%2C%20I%20want%20the%20database%20to%20return%20every%20time%20a%20keyword%20is%20found%20in%20a%20long%20text%20record%20and%20tell%20me%20which%20keyword%20matched.%26nbsp%3B%20I%20think%20I%20have%20created%20a%20query%20to%20find%20all%20the%20matching%20keywords%20in%20the%20long%20text%20records%20but%20cannot%20figure%20out%20how%20to%20tell%20me%20which%20keywords%20matched.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWHERE%20(((Longtext_Table.Longtext_Field)%20Like%20%22*%5BKeywords_Table%5D.%5BKeywords_Field%5D*%22))%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20figure%20out%20how%20to%20get%20Access%20to%20return%20which%20keywords%20were%20found%20in%20each%20long%20text%20record.%26nbsp%3B%20Does%20anyone%20have%20any%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2894894%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2895946%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20a%20field%20of%20keywords%20in%20a%20long%20text%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2895946%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1198542%22%20target%3D%22_blank%22%3E%40Johnqui1790%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EMay%20I%20restate%20the%20requirement%20slightly%20to%20see%20if%20I%20understand%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20create%20a%20query%20that%20filters%20the%20long%20text%20field%20(and%20I'll%20bet%20that%20the%20real%20name%20is%20NOT%20actually%20Longtext_Field%20and%20that%20you've%20aliased%20it%20for%20%22simplicity%22)%20so%20that%20the%20recordset%20returns%20all%20of%20the%20fields%20which%20match%20one%20or%20more%20of%20the%20keywords.%20What%20you%20need%2C%20in%20addition%20to%20the%20field%20itself%2C%20is%20the%20identification%20of%20the%20records%20in%20that%20table%3B%20in%20other%20words%2C%20the%20returned%20recordset%20needs%20to%20include%20two%20fields%3A%3C%2FP%3E%3CP%3Ea)%20the%20field%20in%20question%20and%3C%2FP%3E%3CP%3Eb)%20the%20PRIMARY%20KEY%20of%20the%20record%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20if%20you%20add%20the%20PK%20from%20%22Longtext_Table%22%20to%20the%20query%2C%20you'd%20probably%20get%20what%20you%20want.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi all,

 

I have a list of thousands of keywords in one field.  On another table, I have a field listing thousands of long text records.  Ultimately, I want the database to return every time a keyword is found in a long text record and tell me which keyword matched.  I think I have created a query to find all the matching keywords in the long text records but cannot figure out how to tell me which keywords matched.

 

WHERE (((Longtext_Table.Longtext_Field) Like "*[Keywords_Table].[Keywords_Field]*"));

 

I cannot figure out how to get Access to return which keywords were found in each long text record.  Does anyone have any ideas?

 

1 Reply

@Johnqui1790 
May I restate the requirement slightly to see if I understand correctly.

 

You can create a query that filters the long text field (and I'll bet that the real name is NOT actually Longtext_Field and that you've aliased it for "simplicity") so that the recordset returns all of the fields which match one or more of the keywords. What you need, in addition to the field itself, is the identification of the records in that table; in other words, the returned recordset needs to include two fields:

a) the field in question and

b) the PRIMARY KEY of the record 

 

So, if you add the PK from "Longtext_Table" to the query, you'd probably get what you want.