How Do I Return A Value Of An Entity In A Table That Is Less Than But Closest To The Value In Another Table For Each Element In The Last Table In Sql?

I have two tables in MS Access and I am trying to add a field for one of those tables that tells which record from another table has a value that is less than the first field's val

Solution 1:

I would approach this type of query using a correlated subquery. I think the following words in Access:

SELECT jc.ClassFile,
       (select top 1 ml.Modulefrom ModuleList as ml
        where ml.[Order] < jc.[Order]
       )
FROM JavaClassFileList as jc;

Solution 2:

I'm assuming Order is unique for Module. If it isn't, JavaClassFileRecords may show up multiple times in the resultset.

If no module can be found for a JavaClassFile then it will not show up in the results. If you do want it to show up in cases like that (with a null module), replace INNER JOIN with LEFT OUTER JOIN.

SELECT j.ClassFile, m.ModuleFROM JavaClassFileList j
INNER JOIN ModuleList m
ON m.Order =
    (SELECT MAX(Order)
     FROM ModuleList
     WHEREOrder < j.Order)