title: How do I create a query that returns multiple results from comparisons but with only the first occurence of a specific field?
body: <p>I am writing a SQL query for a DB that organizes the test results of various unit tests.  The unit tests are run on many Java classes and their methods, and each one of those classes are part of one of 15 modules.  I wanted to show the module membership of each Java class using the numbers assigned to all objects (including modules) from the export of the results.  Because the results had the classes grouped by what module they were in, the original table had all objects in one table (for instance, the first 7 entries in the table were methods, followed by the class, followed by 3 more methods, followed by the class that those three methods were in, followed by the module that those two classes were in. They are all numbered in sequence, regardless of what they are (basically the same numbering sequence is applied to classes and modules as is applied to methods)  There are hundreds of methods and classes, with different numbers of them in different modules.  I already came up with queries that are able to separate the methods, classes, and modules into separate tables, but I want to have a field in the class table that says what module a class is part of.  I came up with part of an Into Select Query that I thought would do this, but it complains that it would generate at most 1 record (or something like that, not sure of the specific text).  Here is the query so far, its just a select query right now, as I just wanted to test to make sure it worked (which it isn't):</p>
  
  <pre><code>SELECT JavaClassFileList.ClassFile, ModuleList.Module
  FROM JavaClassFileList, ModuleList
  WHERE ModuleList.Order=
      (SELECT MAX(
          SELECT ModuleList.Order
          FROM ModuleList, JavaClassFileList
          WHERE ModuleList.Order &lt; JavaClassFileList.Order)
       FROM ModuleList, JavaClassFileList);
  </code></pre>
  
  <p>I tried something less complex, but those only returned the first module, nothing else.  I want to return the first module that has an Order value less than the JavaClassFileList Order value, but only one of them (there are a lot of them that are less than, but I want only the first one) for each JavaClass.  I also tried using FIRST instead of MAX, but it had the same issues.  Unfortunately I am having to use Access 2007 for the database (would prefer to use something else but can't at the moment), so that should be taken into account also.</p>
  
  <p>primary keys are ClassFile for JavaClassFileList, Module for ModuleList, and Method for FunctionMethodList. I have not specified any foreignkeyconstraints because I did not see the need to as this is simply to organize already existing data and present it in a more useful form. The foreign key in JavaClassFileList is ModuleMembership (uses Module in ModuleList, but as specified in above problem). Originally, the database was just one table consisting of a list of everything (regardless of whether it was a method, class, or module). They were numbered based on their order in that raw table.</p>
  
  <p>Sample results: in JavaClassFileList, first two classfiles have Order equal to 725 and 720 respectably. These two classes both belong to the same module. That module has Order equal to 719, with the one after equal to 713. I want to return for those two classes the closest module by order that is less than the order value of the classes.</p>