Search code examples
sqlms-accesssubstringms-access-2016

MS Access Query to Take Substring of a Record and Compare To Another Table For Missing

I have two tables of data I need to compare. Table 1 is the "source data" that contains all the files Table 2 needs to contain. One of the entries in each table I'm trying to compare is the data's filepath, to verify that they are in the directory they should be.

The filepath entries in both tables are listed differently, with Table 1 just containing the filename and Table 2 containing the full file path of the file.

Table 1 - "SourceFiles":

SourceFiles

Table 2 - "AllFiles":

AllFiles

Each file name is unique but they all have an ID number that I want to be able to take and use to compare with Table 2. I want to extract this ID number out of the filename and then compare the tables, but I'm having no success when trying to run a query I made to do it.

Here is my query currently (EDITED):

SELECT [SourceFiles].[SourceAudio ID], Mid(Left([SourceFiles].[Filename], InStrRev([SourceFiles].[Filename], ".")-1), InStrRev([SourceFiles].[Filename],"_")+1) As Filename
FROM [SourceFiles] LEFT JOIN [AllFiles] 
ON Mid(Left([SourceFiles].[Filename], InStrRev([SourceFiles].[Filename], ".")-1), InStrRev([SourceFiles].[Filename],"_")+1) = Mid(Left([AllFiles].[Filename], InStrRev([AllFiles].[Filename], ".")-1), InStrRev([AllFiles].[Filename],"_")+1)
WHERE ((Mid(Left([AllFiles].[Filename], InStrRev([AllFiles].[Filename], ".")-1), InStrRev([AllFiles].[Filename],"_")+1) Is Null));

I want the result of this query to look like:

QueryResult

But it comes up with an error saying "Data type mismatch in criteria expression". I went back and verified everything is entered correctly. What am I doing wrong?

Solution

  • Try this:

    Select 
    *
From 
    SourceFiles
Where 
    filename Not In 
    (Select Mid(filename, InstrRev(filename, "\") + 1) From AllFiles)

    That may run slow. If so, write the output of the subquery to a temp table with an index on field filename and use that in the above query:

    Select 
    *
From 
    SourceFiles
Where 
    filename Not In 
    (Select filename From TempTable)