I've tried searching everywhere to see if this is doable, but I haven't found anything... so, here goes.
I am rebuilding a broken Excel workbook that I inherited a few weeks ago. The old version dumps 50,000 records into a spreadsheet for each year and then attempts to execute a few hundred thousand calculations to summarize the data into reports on about 200 sheets within the workbook. You can easily see why it doesn't work. Most of the calculations can be eliminated by using a more efficient SQL query (or queries), and then filtering the resulting recordset(s) and using "copyfromrecordset" to dump the summarized results where they need to be. Plus, there is no need to store the data, this way.
In this case, I am adding a step that I have not needed before -- which is why I need help.
I am using a UNION query to combine several fields from three large tables (2004, 2003, 2002) into one recordset, using GROUP BY and SUM functions to limit the number of rows returned.
Once I have this recordset, I need to retrieve different sets of fields for different reports. For example, I need to select [Industry], FunctionA, FunctionB, etc., for one report; and I need to select [Region], FunctionA, FunctionB, etc., for another report; and so on.
Is there a way to query directly from this recordset? (i.e., SELECT Industry, FunctionA, FunctionB,... FROM rsA WHERE...)