-
Jun 22nd, 2018, 02:04 AM
#1
Thread Starter
Addicted Member
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?
Last edited by kutlesh; Jun 22nd, 2018 at 02:19 AM.
-
Jun 22nd, 2018, 03:26 AM
#2
Thread Starter
Addicted Member
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.
-
Jun 22nd, 2018, 03:31 AM
#3
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
)
-
Jun 22nd, 2018, 03:35 AM
#4
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|