title: How to use subquery to populate column in Access query
body: <p>I am working on an Access database which is used for forecasting purchases and I am trying to create a query in which would give me list of records with valid prices and row sums.</p>
  
  <p>I am running into problems when I try to combine prices to quantities. I have following tables</p>
  
  <p>Table that contains forecasting data (columns not relevant for this query omitted)</p>
  
  <pre><code>need_rows
  ID  product_id  qty use_date
  ----------------------------
  1   1           100 1.1.2014
  2   1           50  15.1.2014
  ... 
  </code></pre>
  
  <p>And table for prices</p>
  
  <pre><code>prices
  ID  product_id  price   valid_from
  ----------------------------------
  1   1           1       1.12.2013
  2   1           2       24.12.2013
  3   1           5       10.1.2014
  ... 
  </code></pre>
  
  <p>Query resulst should be something like below:</p>
  
  <pre><code>result of query
  product_id  use_date    qty price   sum
  ---------------------------------------
  1           1.1.2014    100 2       200
  1           15.1.2014   50  5       250
  ...
  </code></pre>
  
  <p>Meaning that I need to fetch valid price to each of the rows based on the use_date from need_rows and valid_from date from prices. Valid prices is the one that has valid_from date equal or most recent to use_date.</p>
  
  <p>Below is one of the approaches I have tried with no luck.</p>
  
  <pre><code>SELECT prices.price
  FROM prices
  WHERE (((prices.product_id)=[product_id]) AND ((prices.valid_from)=
  (SELECT Max(prices.valid_from) AS valid
  FROM prices
  WHERE (((prices.product_id)=[product_id]) AND ((prices.valid_from)&lt;=[use_date]));).));
  </code></pre>
  
  <p>Any help is appreciated!</p>