Microsoft Access 2016 – Need to combine SQL query using JOIN …. ON Col1 MOST LIKE Col2

I am working in Microsoft Access 2016. I have a data table [Data] that contains many thousand rows of data. I have a lookup table [Lookup] that contains all of the project IDs that are known. These tables should combine on the project ID, which is a string field. Normally, I would join these tables using:

SELECT * FROM [Data] LEFT JOIN [Lookup] ON [Data].[ProjectID] = [Lookup].[ProjectID]

The issue is that the [Data] project ID field is a much longer string that the lookup. As an example

Data:

• PROJECT.TS.01.004.005

Lookup:

• PROJECT.TS
• PROJECT.TS.01
• PROJECT.TS.02

I tried using the following:

 SELECT * FROM [Data] LEFT JOIN [Lookup] ON [Data].[ProjectID] LIKE [Lookup].[ProjectID] + '*'

The only issue is that there are multiple entries in the lookup table that are “LIKE” the data field. I need to be able to join on the entry that is not only “LIKE” but has the greatest length of all those that are “LIKE”.

It really seems like what I need is to call a function that is “MOST LIKE” but that doesn’t exist to my knowledge in SQL syntax.

Any advice on how to join these two tables effectively?

Answer

Consider:

SELECT * FROM [Data] LEFT JOIN [Lookup] ON [Data].[ProjectID] LIKE [Lookup].[ProjectID] + '*' 
WHERE Len(Lookup.ProjectID) = DMax("Len(ProjectID)", "Lookup", "'" & Data.ProjectID & "' LIKE ProjectID & '*'");

Or

SELECT * FROM [Data] LEFT JOIN [Lookup] ON [Data].[ProjectID] LIKE [Lookup].[ProjectID] + '*' 
WHERE Len(Lookup.ProjectID) = (
    SELECT Max(Len(Lookup.ProjectID)) FROM Lookup WHERE Data.ProjectID LIKE Lookup.ProjectID & "*");