title: Microsoft Access selecting records from another table matching current record
body: <p>It seems I've hit a brick wall in MS Access 2010.</p>
  
  <p>It's kind of hard to explain what I'm trying to achieve, so I'll start with a basic example. Let's say we have two tables: A and B.</p>
  
  <p>A:</p>
  
  <pre><code>ID     Price    Item
  1      5        ABB
  2      4        ATV
  3      2        CCC
  </code></pre>
  
  <p>B:</p>
  
  <pre><code>ID     Price limit    Chosen item
  1      3    
  2      4
  3      5
  4      6
  </code></pre>
  
  <p>What I'm trying to achieve is create a Relation from table A to B. Each record in table B has to have associated table A record. It should be based on table A field 'Price' and table B field 'Price limit' in a way that the price of selected record from Table A is lower than the price limit imposed in table B record. </p>
  
  <p>That is, the possible table A records for the first table B record is only CCC, for second - ATV and CCC, while for third and fourth all records are valid.</p>
  
  <p>As far as my limited access knowledge goes, I've figured that I should write a query in "Chosen item" field row source property. I've tried writing it myself, however, without success. Here's what I've come up with:</p>
  
  <pre><code>SELECT [Table A].[ID], [Table A].[Item] 
  FROM [Table A] 
  WHERE [Table A].[Val] &gt; [Val]; 
  </code></pre>
  
  <p>But it does not work. Could somebody please point me to the right direction?</p>