recordsets question

[torok]

Sorry, long question but really not that complicated:

I have an aggregate sum query on an orders table (in code) that, given the appropriate parameter (an invoice number), will come up with one row of data (4 items - subtotal, total, and the totals of 2 taxes).

I want to make an update query that sticks these values into my Invoice table (I do this at invoice creation time so that I never recalculate later; if tax rates change in the future I want my invoice records to be unaffected).

If I went (UPDATE invoices SET w = query1.w, x=query1.x, y=query1.y, z=query1.z) won't it run query1 FOUR times, or does Access cache queries? (Note that I would have to put full SELECT statements in place of "query1" every time so that I can give it a parameter -> really long query)

If not, how do I use a recordset to cache the results of query1 and then pick out its values to update my Invoices table?

Thanks for taking the time to read this!

 

[FoFa]

Join your summary query to your update query and on the set statements reference the fields from your summary query

 

[torok]

That's what I was going to do - but won't it run my summary query 4 times then? Or does it cache results?

Also, the resulting SQL statement will be fantastically long - is there a max string length for SQL statements in code?