title: Select statement within select statement SQL and access
body: <p>I am trying to select the maximum price of a product that has been sold within the database, I have three tables (<code>tbl_CarForSale</code>, <code>tbl_Commision</code> and <code>tbl_Salesman</code>).</p>
  
  <p>What I want to do is select the Maximum price of a Car that has been sold, and then, using that record, be able to calculate the commision earned, and link it to the salesman. I have the following and it returns the Maximum car price, but I don't know how to directly use the returned record to get the other information possible.</p>
  
  <pre><code>SELECT Max(tbl_CarForSale.carprice)
  FROM (SELECT tbl_CarForSale.*, tbl_Salesman.*,  from tbl_CarForSale, 
  WHERE bolSold = true)
  </code></pre>
  
  <p>I am guessing that I would need to put this into another <code>SELECT</code> statement and nest it, but I am not sure how to do this.</p>
  
  <p>Edit:</p>
  
  <p>As requested the field names are here. </p>
  
  <pre><code>tbl_salesman: name
  tbl_CarForSale: registration, soldDatem salesmanNo, carPrice, bolSold
  tbl_Commision: minValue, maxValue, comAvliable
  </code></pre>
  
  <p>Basically, the idea behind the Commission table is that a salesman earns commission based on the price of the car, and therefore I have a query that looks at the car price and will return how much commission the salesman has earned</p>