sql - Optimising a VBA procedure that iterates over two different tables of data -

i have 2 sql tables pricetbl , paymenttbl. due fact prices item change everyday, need update paymenttbl prices of right time. if transaction made after date-effective of product's price, transaction price in paymenttbl same latest product's price (as long date-effective smaller transaction date). don't want limit myself here, want iterate on whole table make changes data every single transaction has proper price.

pricetbl    pid| price| date effective --------------------------- 1  |   10 | 01-12-2014 2  |   20 | 01-02-2015 3  |   20 | 02-12-2014 2  |   40 | 20-03-2015 1  |   50 | 02-03-2015 4  |   34 | 20-02-2015 1  |   40 | 25-05-2015  paymenttbl  payid | pid | transdate | price --------------------------------   1   |  1  | 02-12-2014| 05   2   |  1  | 04-03-2015| 10   3   |  2  | 21-04-2015| 35   4   |  3  | 03-12-2014| 15 

expected table paymenttbl after running query

payid | pid | transdate | price --------------------------------   1   |  1  | 02-12-2014| 10   2   |  1  | 04-03-2015| 50   3   |  2  | 21-04-2015| 40   4   |  3  | 03-12-2014| 20 

what fastest , easiest way retrieve , update data? current way takes 30 minutes update table since iterate on paymenttbl iterate on pricetbl , compare each item in paymenttbl.

my code:

function updateworktodateeff()     dim rt dao.recordset     dim wt dao.recordset     dim db dao.database     dim sqlrt string     dim sqlwt string      set db = currentdb      sqlrt = "select * pricetbl (dailyrate not null)  , (dateeffective not null) order id, dailyrate"     sqlwt = "select * paymenttbl (id > 0) order id, date"      set rt = db.openrecordset(sqlrt)     set wt = db.openrecordset(sqlwt)      while not wt.eof          while not ratetable.eof             if rt!id = wt!id                 if rt <= wt!date                          wt.edit                         wt!rate = ratetable!dailyrate                         wt.update                   end if             elseif ratetable!id > worktable!id                 exit             end if             rt.movenext         loop         rt.movefirst         wt.movenext 'move next worktbl record     loop end function 

simply iterating/retrieving data wouldn't it. need able alter it.

the efficient way perform update not use recordsets , go row-by-row, use sql set-based update. tried writing single update query there, ms-access got fussy query not being updateable, instead wrote 2 queries: 1 write temporary table (tempnewprices) , 1 update paymenttbl using temporary table.

here's first query create temporary table includes new column newprice:

select paymenttbl.payid, paymenttbl.pid, paymenttbl.transdate, paymenttbl.price,  (   select [price]    pricetbl pr    pr.pid = paymenttbl.pid    , pr.[date effective] = (     select max([date effective])      pricetbl pr2      pr2.pid = pr.pid      , pr2.[date effective]< paymenttbl.[transdate]   ) ) newprice tempnewprices paymenttbl; 

and here's update query change price value in paymenttbl:

update tempnewprices  inner join paymenttbl on tempnewprices.payid = paymenttbl.payid  set paymenttbl.price = [newprice]; 

this should more efficient looping through recordsets.