More on the Weird VB code/Access 2000

[cafeenman]

OK, it works for my brother. But he said that the table he is updating from has almost 20,000 records and the code has been running for 15 minutes and is still going.

My question now (for all you SQL geniuses) is can all these queries and recordsets be combined into a single update query using sub-queries?

sSQL_NEW = "SELECT * FROM New_Table"
Set RST_NEW = CurrentDb.OpenRecordset(sSQL_NEW, dbOpenDynaset) ' works fine for me
 
sSQL_LG = "SELECT * FROM Big_Table ORDER BY ID ASC"
Set RST_LG = CurrentDb.OpenRecordset(sSQL_LG, dbOpenDynaset)
 
With RST_LG
  .MoveFirst
  Do Until .EOF
    sSQL_SM = "SELECT TOP 1 * FROM Small_Table WHERE ID < " & !ID & " ORDER BY ID DESC"
    Set RST_SM = CurrentDb.OpenRecordset(sSQL_SM, dbopensnapshot)
    RST_NEW.AddNew
       RST_NEW!ID = !ID
       RST_NEW!Loan = !Loan
       RST_NEW!Pmt = !Pmt
       RST_NEW!Inv = RST_SM!Inv
    RST_NEW.Update
    .MoveNext
  Loop
End With

[DerFarm]

If I'm reading this correctly, you want the maximum ID from small
table that is not equal to the current big table ID and then you do
stuff to it. Is this correct?

[MetallicaD]

If you want to do something similar to what DerFarm said.. you dont need to loop through everything just to find that value.. you can have SQL pull that value for you.. give us, in text format, what you are looking for and someone will create the SQL for you.. should be that simple

-mcd

[cafeenman]

Right. There are three tables. The third table is populated from a combination of items from the 1st and 2nd tables.

The items from the 2nd table are selected where the ID of the second table is the Greatest ID that is less than the ID of the 1st table. That's why we did a TOP 1 and SORT DESC.

I have no idea how to write this query as an update query. My brother is experimenting with sending the data to excel and seeing if it can do it faster.