title: Lookup values based solely on minumum value of range
body: <p>I'm wanting to place values within a range given only a minimum value, similar to using a VLOOKUP/HLOOKUP in Excel using the "FALSE" criteron. </p>
  
  <p>As seen below, TableScore lists the low-end cutpoints (CutpointVal) for a value to be assigned a specific number of points (the minimum value in a range). The below SQL code accomplishes this in two steps, with the first query generating a datasheet that includes a high value for each low value, thus creating a full range. </p>
  
  <p>However, this is a somewhat clunky way of doing this, especially when trying to iterate this many times. <strong>The original table (TableScore) cannot be altered to include high values.</strong> Is there a way to accomplish a similar mechanism with only one query?</p>
  
  <p>Main</p>
  
  <pre><code>ID              Score
  72625           2.5
  78261           3.2
  82766           4.7
  58383           0.3
  </code></pre>
  
  <p>TableScore</p>
  
  <pre><code>CutpointVal     Points
  0               0
  0.3             1
  1.2             2
  2.7             3
  3.4             4
  </code></pre>
  
  <p>Upper and lower range query (RangeQry):</p>
  
  <pre><code>SELECT a.CutpointVal AS LowVal, Val(Nz((SELECT TOP 1 [CutpointVal]-0.001
  FROM TableScore b
  WHERE b.Points &gt; a.Points
  ORDER BY b.Points ASC),9999)) AS HighVal, a.Points
  FROM TableScore AS a
  ORDER BY a.Points;
  </code></pre>
  
  <p>Range assignment query:</p>
  
  <pre><code>SELECT Main.ID, Main.Score, RangeQry.LowVal, RangeQry.HighVal, RangeQry.Points AS PTS
  FROM RangeQry, Main
  WHERE (((Main.Score) Between [RangeQry].[LowVal] And [RangeQry].[HighVal]));
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>ID        Score        Points
  72625     2.5          2
  78261     3.2          3
  82766     4.7          4
  58383     0.3          1
  </code></pre>