Nested MS Access SQL Query Using 'LIKE' Operator

Question

I have been given a large number of files and what is effectively a 'document index' in an excel file.

I am trying to identify if we actually received all the files listed in the index.

My problem is that not all the electronic filenames are correct.

In some cases the electronic filename has had a description or a revision added, therefore a direct lookup (JOIN) is not possible and I would like to use a nested LIKE statement:

An example of the problem is I cannot match the objects below:
tblINDEX.idxFilename - tblFilename.dirFilename
RR-ASF-00-0111-000075 - RR-ASF-00-0111-000075Construction Details.pdf

The below query (JOIN) only finds direct matches:

SELECT tblINDEX.idxFilename, tblFilename.dirFilename
FROM tblINDEX LEFT JOIN tblFilename ON tblINDEX.idxFilename = dirFilename
WHERE tblFilename.filename Is Null Or tblFilename.filename Is Not Null;

The query below (LIKE) does not show the blanks / nulls (missing filenames).

SELECT tblINDEX.idxFilename, tblFilename.dirFilename
FROM tblFilename, tblINDEX
WHERE tblFilename.dirFilename Like idxFilename & '*' Or tblFilename.dirFilename Is Null;

My nested 'SELECT' returns a syntax error in the nested SELECT statement.

SELECT tblINDEX.idxFilename
    ,(
        SELECT tblFilename.filename
        FROM tblFilename
        WHERE tblFilename.filename Like tblINDEX.idxFilename & '*'
        ) AS 'idxFilename_FN'    
    FROM tblINDEX
;

I know this nested query approach works in T-SQL, but struggling to get it working in MS Access.

iDevlop iDevlop · Accepted Answer · 2018-10-08T09:10:08

Not tried, but what about this?

SELECT tblINDEX.idxFilename, tblFilename.dirFilename
FROM tblINDEX LEFT JOIN tblFilename ON tblFilename.filename Like tblINDEX.idxFilename & '*'  
WHERE....

It has to be done in SQL view and will prevent switching back to design view, but it should work.

Nested MS Access SQL Query Using 'LIKE' Operator

1 Answers