Join or match data on text within string from two tables (1 Viewer)

[sxschech]

Would like query to get data from one table into the query from another table based on matching a word within a string. There are a few records where the functions I used didn't quite get there and wondering how might be able to get the stragglers.

In this example containing the relevant fields from each table/query trying to match tblData.Location to tblLocation.Location on <Hendy> since that keyword is common to both fields in order to get the Segment Code which can then derive the Segment from tblSegment <Segment 3: MP 28.98 to MP 43.80>

End Result / what is needed:
qrySegmentLocation

Segment		Filename
Segment 3: MP 28.98 to MP 43.80		Report XYZ 20231026.pdf

tblData
Location		Filename
MTN View/Hendy Set out Track		Report XYZ 20231026.pdf

tblLocation
SegmentCode		Location
S3		WPC 17 at CP Hendy

tblSegment
SegmentCode		Segment
S3		Segment 3: MP 28.98 to MP 43.80

Since I couldn't join the tables I used dlookup after doing some string manipulation. This is what I used and it got about 80% of the data.

IIf(IsNull([location]),[Inspection255],[Location]) AS LocationInspec,
IIf([location] Is Null,Mid(regexpatternextract([Inspection255],"MP\s\d{1,2}\."),4,3),IIf(Left([Location],3)="MP ",Mid([Location],4,3),[Location])) AS ParseLoc1,
Replace(Replace([ParseLoc1]," Station","")," Crossing","") AS ParseLoc2,
DLookUp("Segment","qrySegmentLocations","SegmentLocation Like '*" & [ParseLoc2] & "*'") AS Segment,

 

[MajP]

Do you have a list of keywords? This example looks like they are checkpoints and you would have a list. If not how do you avoid matching words like "out, at, to..."? Can you all a field to each table "clean location" where you read through the table and basically tokenize the data getting rid of extraneous words, or even creating child records. Example I would get Mtn View, Hendy as child records for tblData. And just Hendy for tbllocation.

 

[sxschech]

Assuming this was your suggestion, I saved the records that came up empty (which turned out to be a manageable 24 unique records) into a table and then added a field and manually pasted the Segment into it. After that, added this exception table to the query and defined it as
Segment: IIf([SA].[Segment] Is Null,[tblParseLocExceptions].[Segment],[SA].[Segment]) AS Segment.

When new exceptions pop up, then I'll add them to the exception table.