sql - Union field Table from a Table in Access -

i have big table in access

id idparent  timerecord value 1   1         00.00.01    10     2   1         00.00.05    12     3   2         00.00.03    4       4   2         00.00.10    4 5   3         00.00.05    5    6   3         00.00.10    6  7   4         00.00.01    100 8   4         00.00.04    110 9   4         00.00.09    120 

need union table idparent = 4 , synchro field timerecord

result:

id idparent  timerecord value  valueidparent4 1   1         00.00.01    10    100         // 100 because 00.00.01 between 00.00.01 ,  00.00.04 2   1         00.00.05    12    110         // 110 because 00.00.05 between 00.00.04 ,  00.00.09 3   2         00.00.03    4     100         // 100 because 00.00.03 between 00.00.01 ,  00.00.04 4   2         00.00.10    4     120         // 120 because 00.00.10 between 00.00.09 ,  last 5   3         00.00.05    5     110         // 110 because 00.00.05 between 00.00.04 ,  00.00.09 6   3         00.00.10    6     120        // 120 because 00.00.10 between 00.00.09 ,  last 

how result table in sql?

select t1.id id, t1.idparent idparent,            t1.timerecord timerecord, t1.value value,                coalesce( (select max(value)        mytable t2         t2.timerecord <= t1.timerecord          , t2.idparent = 4),      (select max(value) mytable))                valueidparent4         mytable t1     not t1.idparent = 4 

sqlfiddle: http://sqlfiddle.com/#!4/472f2/5

we highest amount of points time lower time (so closest time lower time) use coalesce function highest possible points can in case time high specific amount of points

more coalesce @ : https://msdn.microsoft.com/en-us/library/ms190349.aspx

in access 2010 need use function nz

select t1.id id, t1.idparent idparent,            t1.timerecord timerecord, t1.newvalue newvalue,           nz(  (select max(newvalue) mytable t2 t2.timerecord <= t1.timerecord , t2.idparent = 4), (select max(newvalue) mytable) )   valueidparent4  mytable t1 not t1.idparent = 4