How to make multi-query result in Access?

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:
      TotalLines=2

Output to CSV/Excel:
     TotalLines=2
     <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!
intsupAsked:
Who is Participating?
 
als315Commented:
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
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Close #FileO
End Sub

Open in new window

In example csv is tab separated, but you can change Tab to ","
0
 
intsupAuthor Commented:
als315,

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?
0
 
als315Commented:
You can add button to some form and put this code to button's press event.
0
 
intsupAuthor Commented:
thanks!  i will try it out and hope for the best..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.