dcsimg
Results 1 to 4 of 4

Thread: Output several query results in same xls worksheet?

  1. #1

    Thread Starter
    Lively Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    120

    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.

  2. #2

    Thread Starter
    Lively Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    120

    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.

  3. #3
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,658

    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
    )

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,868

    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
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

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
  •  



Featured


Click Here to Expand Forum to Full Width