Output several query results in same xls worksheet?
I have two select queries which have same column structure. The first query returns summary of the data and the second returns the actual data.
I want to export the results from both queries in the same Excel worksheet. The problem is, when I use the SQL code snippet which exports query result in xls file, the second query complains with this error:
Code:
Table 'MyWorkSheet' already exists., error number: 3010
Here are the queries:
Code:
SELECT
Count(MyTable.Col1) AS CountOfCol1,
MyTable.Col2 INTO
[MyWorkSheet] IN ''[Excel 8.0;Database=C:\Documents and Settings\MyCol2Name\My Documents\MyExcelFile.xls]
FROM MyTable GROUP BY MyTable.Col2;
SELECT
MyTable.Col1,
MyTable.Col2 INTO
[MyWorkSheet] IN ''[Excel 8.0;Database=C:\Documents and Settings\MyCol2Name\My Documents\MyExcelFile.xls]
FROM MyTable;
Is there a way to export results of both queries into the same Excel worksheet?
Re: Output several query results in same xls worksheet?
Not sure if there is a direct way to do this, but you can create temporary table with Jet SQL(Access SQL).
Create separate INSERT queries which will insert the data needed into this temporary table and then just one SELECT query with all data into an Excel file.
Then you can also drop your temporary table with VBA/JET SQL.
Re: Output several query results in same xls worksheet?
You can try to use a UNION query, something like this:
Code:
SELECT col1, col2
INTO [MyWorkSheet] IN ''[Excel 8.0;Database=C:\Documents and Settings\MyCol2Name\My Documents\MyExcelFile.xls]
FROM
(
SELECT Count(Col1) AS Col1, Col2 FROM MyTable GROUP BY MyTable.Col2
UNION
SELECT Col1, Col2 FROM MyTable
)
Re: Output several query results in same xls worksheet?
Exactly my thoughts.
You could even insert something like a separating line within the SQL
Code:
SELECT col1, col2
INTO [MyWorkSheet] IN ''[Excel 8.0;Database=C:\Documents and Settings\MyCol2Name\My Documents\MyExcelFile.xls]
FROM
(
SELECT Count(Col1) AS Col1, Col2 FROM MyTable GROUP BY MyTable.Col2
UNION
SELECT TOP 1 '---', '---' FROM MyTable
UNION
SELECT Col1, Col2 FROM MyTable
)
EDIT: The TOP N predicate is for MS Access. If you're exporting from another DBMS look up the mechanism to return only one row