How to make multi-query result in Access?

Posted on 2012-08-28
Last Modified: 2012-09-07
I can't provide a specific query because i'm still working on the formatting so i'll provide a generalization and thank you in advance for your understanding.

I have TWO queries that I need output to Excel.

Query 1 Result View:
     Line     Customer     ProdDesc      TotalAmount
     1          Acme            Widgets        $1,000.00
     2          Beta              Tools            $500.50

Query 2 Result Veiw:

Output to CSV/Excel:
     <Carriage Return>
     Line     Customer     ProdDesc      TotalAmount
     1          Acme            Widgets        $1,000.00
     2          Beta              Tools            $500.50

I assume i simply make Query2 a MAKETABLE query and then Query1 an APPEND to existing CSV file but don't know how to do that or if that's the correct method.

Another option is to create a blank table and append these two queries, plus a third query that will add the words "Line     Customer     ProdDesc      TotalAmount" before appending Query1 and then exporting the table to csv.

but any suggestions would be helpful.

thanks for your help!
Question by:intsup
    LVL 39

    Accepted Solution

    Csv is text file and you can write it with this sub:
    Sub write_csv()
    Dim fileN As String, FileO As Integer, RC As Long
    Dim rst As DAO.Recordset
    Dim Qry As String
    Qry = "Query1" ' your query
    fileN = "c:\tmp\test.csv" ' output file
    FileO = FreeFile
    RC = DCount("*", Qry)
    Set rst = CurrentDb.OpenRecordset(Qry)
    Open fileN For Output As #FileO
    Print #FileO, "TotalLines="; RC
    Print #FileO, "Line"; Tab; "Customer"; Tab; "ProdDesc"; Tab; "TotalAmount"
    Do Until rst.EOF
     Print #FileO, rst!Line; Tab; rst!Customer; Tab; rst!ProdDesc; Tab; rst!TotalAmount
    Set rst = Nothing
    Close #FileO
    End Sub

    Open in new window

    In example csv is tab separated, but you can change Tab to ","

    Author Comment


    if that works then that is exactly what i need.

    i am using access 2007, where do i put this sub?  (is it a module?)
    how do i call this procedure?
    LVL 39

    Assisted Solution

    You can add button to some form and put this code to button's press event.

    Author Comment

    thanks!  i will try it out and hope for the best..

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now