title: Two Tables With Effective Dates In Which Data Can Vary Independently
body: <p>I am developing a database in MS Access 2010.  I have two tables, one for Deals (tblDeals), and one for the Deal Items (tblDealItems).</p>
  
  <p>I need to track the history for the data in these tables, so I am using a DateEffective field  and DateEnd field in each of them.</p>
  
  <p>The problem is that the data in each of the two tables can vary independently on different dates, which makes it difficult (impossible for me, actually) to return a recordset which has the true history of the series of events which occur during the deal.</p>
  
  <p>The tables have the following data for one particular deal, "Deal 3":</p>
  
  <pre><code>tblDeals:
  DealID DealName AssetClassID DateEffective DateEnd
  ------ -------- ------------ ------------- -------
  3      Deal 3   3            1 Jan 2010    1 Jul 2011
  3      Deal 3   2            1 Jul 2011    1 Oct 2011
  3      Deal 3   1            1 Oct 2011 
  
  tblDealItems:
  DealItemID DealID CategoryID ParticipantID Amount DateEffective DateEnd
  ---------- ------ ---------- ------------- ------ ------------- -------
  13         3      3          2             1500   1 Jan 2010    1 Jun 2011
  13         3      1          2             1500   1 Jun 2011    6 Jun 2011
  13         3      1          2             6000   6 Jun 2011    1 Sep 2011
  13         3      3          2             6000   1 Sep 2011 
  </code></pre>
  
  <p>So the actual history of the Deal is this (created manually - this (with the exception of the "Description" column - which is there so it is obvious what has changed in each row) is what I would like to return):</p>
  
  <pre><code>Date       (Description)         DealID DealName AssetClassID CategoryID ParticipantID Amount
  ---------- --------------------- ------ -------- ------------ ---------- ------------- ------
  1 Jan 2010 (Deal 3 Created)      3      Deal 3   3            3          2             1500
  1 Jun 2011 (Category Changed)    3      Deal 3   3            1          2             1500
  6 Jun 2011 (Amount Changed)      3      Deal 3   3            1          2             6000
  1 Jul 2011 (Asset Class Changed) 3      Deal 3   2            1          2             6000
  1 Sep 2011 (Category Changed)    3      Deal 3   2            3          2             6000
  1 Oct 2011 (Asset Class Changed) 3      Deal 3   1            3          2             6000
  </code></pre>
  
  <p>Obviously, if I join the two tables on their shared key, I get a table with twelve rows (3 * 4) instead of the six rows which describe the history.</p>
  
  <p>I know that I need to be able to join the tables together in some way so that I can generate the true history of the deal, but I just don't know how! (I'm really an Excel guy, not a SQL one!)</p>
  
  <p>I think I have worked out exactly what is required to solve the problem specified above:</p>
  
  <p>For each date in qryDates, I need to find the largest tblDeals.DateEffective which is less than or equal to that date and the largest tblDealItems.DateEffective which is less than or equal to that date.</p>
  
  <p>I the need to return the row from a query joining tblDeals and tblDealItems which has those two exact dates.</p>
  
  <p>Any help gratefully received.</p>