title: How to construct price time series from price changes using MS-Access 2007 queries?
body: <h2>Background</h2>
  
  <p>I've got a table of price <strong>changes</strong> <code>tblPriceChanges</code> of various items <code>tblItems</code> (example below - data made up):</p>
  
  <pre><code>[tblPriceChanges]:
  Timestamp       Item    Price
  9AM 01/01/2013  Orange  50p
  9AM 01/01/2013  Apple   30p
  2PM 01/01/2013  Pen     80p
  2PM 02/01/2013  Orange  55p
  2PM 02/01/2013  Pen     85p
  9AM 03/01/2013  Apple   25p
  9AM 05/01/2013  Pencil  10p
  9AM 05/01/2013  Pen     70p
  2PM 05/01/2013  Pencil  15p    &lt;- Notice there can be multiple price changes on the same day
  ...
  
  [tblItems]:
  Item    Category    Ratio
  Orange  Fruit       1
  Apple   Fruit       3
  Pen     Stationary  2
  Pencil  Stationary  5
  ...
  </code></pre>
  
  <h2>Problem</h2>
  
  <p>The end result is that I want to be able to see how the <strong>average price of each category changes through time</strong> - for example:</p>
  
  <p><img src="https://i.stack.imgur.com/8Tabo.png" alt="enter image description here"></p>
  
  <p>Specifically, the average price series of Fruits, for instance, should be calculated as the weighted average of Orange and Apple prices in a <code>Ratio</code> of 1:3. So in the end I'm looking to generate (via some combination of queries) the following table for the underlying data of the chart:</p>
  
  <pre><code>Timestamp   Fruit   Stationary
  01/01/2013  40.0    80.0
  02/01/2013  55.0    85.0
  03/01/2013  50.0    85.0
  04/01/2013  50.0    85.0
  05/01/2013  50.0    75.0
  ...
  (this data is also made up and so probably not consistent with the original example)
  </code></pre>
  
  <p>I've managed to get an inner join on the two tables, but not too sure how to proceed. My main problem is how to handle days with no prices changes such as 01/04/13. The average prices still exist, but is not getting picked up by any query I try.</p>
  
  <p>So how to use queries to construct data for the chart?</p>