What happens when I use a query field in the design of another query......is the first query result refreshed when I run the second query or does it just use some kind of stored result of the first query to apply to the second query when I run it?
If it does rerun the first query when I execute the second query, then how can I avoid that? Do I need to cut and past the results of the first query to a table and reference that in the second query so I don't bog down the server?
That's a very good question! And one that gave me a lot of grief until I figured out a simple way to avoid it, and for the exact reason you mention.
Instead of having the Query1 as a Select query, and putting it into Query2 as a re-generating recordset, change Query1 to be a Make-table query, which will delete and override the results each time you run it, and have the Temp Table as a recordset in Query2. That way, you can run Query1 once, and Query2 any number of times based on the same results.
This approach works wonders when you're in the testing / debugging stage of development. However, bear in mind that if you're running this in VBA code that is generating a periodic report, for example, and Query1 is getting its data from a live file, you really do want to re-run Query1 every time you run the program.