title: cross reference nearest date data
body: <p>I have three table <code>ElecUser</code>, <code>ElecUsage</code>, <code>ElecEmissionFactor</code><br>
  <code>ElecUser:
   UserID  UserName
   1       Main Building
   2       Staff Quarter</code></p>
  
  <p><code>ElecUsage:
   UserID Time      Amount
   1      1/7/2010  23230
   1      8/10/2011 34340
   1      8/1/2011  34300
   1      2/3/2012  43430
   1      4/2/2013  43560
   1      3/2/2014  44540
   2      3/6/2014  44000</code></p>
  
  <p><code>ElecEmissionFactor:
   Time        CO2Emission 
   1/1/2010    0.5
   1/1/2011    0.55
   1/1/2012    0.56
   1/1/2013    0.57</code></p>
  
  <p>And intended outcome:<br>
  <code>UserName    Time       CO2
   1           2010       11615
   1           2011       37752 (34340*0.55 + 34300*0.55)
   1           2012       24320.8 
   1           2013       24829.2
   1           2014       25387.8
   2           2014       25080</code></p>
  
  <p>The logic is ElecUsage.Amount * ElecEmissionFactor.
  If same user and same year, add them up for the record of that year.
  My query is:<br>
  <code>SELECT  ElecUser.UserName, Year([ElecUsage].[Time]), SUM((ElecEmissionFactor.CO2Emission*ElecUsage.Amount)) As CO2
  FROM ElecEmissionFactor, ElecUser INNER JOIN ElecUsage ON ElecUser.UserID = ElecUsage.UserID
  WHERE (((Year([ElecUsage].[Time]))&gt;=Year([ElecEmissionFactor].[Time])))
  GROUP BY ElecUser.UserName, Year([ElecUsage].[Time])
  HAVING Year([ElecUsage].[Time]) = Max(Year(ElecEmissionFactor.Time));</code></p>
  
  <p>However, this only shows the year with emission factor.<br>
  The challenge is to reference the year without emission factor to the latest year with emission factor.<br>
  Sub-query may be one of the solutions but i fail to do so.<br>
  I got stuck for a while. Hope to see your reply.<br>
  Thanks</p>