title: MS-Access 2007 query with conditions on two fields
body: <p>I have a dataset with unique records that includes 2 fields: RName and RVer.  A subset looks like this:</p>
  
  <pre><code>RName, RVer
  Joe,   2 
  Joe,   3
  Joe,   4
  Joe,   5
  Bill,  1
  Bill,  5
  </code></pre>
  
  <p>I want to query on a Version number "MaxVer", returning each instance of a RName whose RVer is the greatest one, less than or equal to MaxVer.  If a RName has no RVer less than or equal to MaxVer, then that RName is not returned at all.  </p>
  
  <p>Here are examples of the query and the desired return:</p>
  
  <pre><code>MaxVer  ---&gt; Returns ---&gt;   Dataset Returned
  1                           Bill,1
  2                           Bill,1  and  Joe,2
  3                           Bill,1  and  Joe,3
  4                           Bill,1  and  Joe,4
  5                           Bill,5  and  Joe,5
  6                           Bill,5  and  Joe,5
  </code></pre>
  
  <p>This is the closest I've come so far, but it's not very close at all:</p>
  
  <pre><code>SELECT DISTINCT MainTbl.RName, MainTbl.RVer
  FROM MainTbl
  WHERE MainTbl.RVer &lt;= MaxVer;
  </code></pre>
  
  <p>Using MaxVer=4 as a test case, this SQL query returns 4 records:</p>
  
  <pre><code>Bill,1   and   Joe,2   and   Joe,3   and Joe,4
  </code></pre>
  
  <p>If someone would help me out here, I'd surely appreciate it!</p>
  
  <p>Thanks,
  Dave</p>