Unanswered: Embedded Workbooks With Multiple Queries
It's pretty easy to embed an Excel worksheet with the Pivot Table Wizard in Access 2000. What is not so immediately apparent is how to have more than one Pivot Table based on separate sources in the same workbook without requiring external query files.
1. Create two embedded Pivot Tables based on different tables or queries (two forms) using Form, New, Pivot Table Wizard.
2. Open them both for editing using the "Edit Embedded Object" button the wizard puts on the forms.
3. With both sheets open in Excel, select the one you want to include in the other's workbook, and "Edit, Move or Copy Sheet" will present you with the option.
4. I checked it out with the Pivot Table toolbar "Show Fields" open. When you switch between worksheets, you should see the available fields change if the Pivot Tables are based on different tables or queries.
I had tried using a union query, but couldn't break out the data the way I wanted to. Also, it seems Excel perceives Access union queries as OLAP data sources and disables drilldown.