sql - cross reference nearest date data -

i have 3 table elecuser, elecusage, elecemissionfactor
elecuser: userid username 1 main building 2 staff quarter

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

elecemissionfactor: time co2emission 1/1/2010 0.5 1/1/2011 0.55 1/1/2012 0.56 1/1/2013 0.57

and intended outcome:
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

the logic elecusage.amount * elecemissionfactor. if same user , same year, add them record of year. query is:
select elecuser.username, year([elecusage].[time]), sum((elecemissionfactor.co2emission*elecusage.amount)) co2 elecemissionfactor, elecuser inner join elecusage on elecuser.userid = elecusage.userid (((year([elecusage].[time]))>=year([elecemissionfactor].[time]))) group elecuser.username, year([elecusage].[time]) having year([elecusage].[time]) = max(year(elecemissionfactor.time));

however, shows year emission factor.
challenge reference year without emission factor latest year emission factor.
sub-query may 1 of solutions fail so.
got stuck while. hope see reply.
thanks

try this..

-- not tested

select t1.id, year(t1.time) time, sum(t1.amount*t2.co2emission) co2  elecusage t1  left outer join elecemissionfactor t2 on (year(t1.time) = year(t2.time)) group year(t1.time), t1.id 

use sub query corresponding factor in way

    select t1.id,          year(t1.time) time,          sum(t1.amount*                     (                     select top 1 co2emission elecemissionfactor t2                      year(t2.time) <= year(t1.time) order t2.time desc                     )         ) co2  elecusage t1  group year(t1.time), t1.id