Great question! A lot of times I will create a query first, which contains all the output, just like I want in my final output file. Then I will use a single DoCmd.TransferText command to output my query to the CSV file.
If you are joining data from two tables together, you will use what is called a Union Query. This can be a little more confusing to create because you need to type in the SQL yourself instead of dragging and dropping in the visual query editor. But once you get the hang of it, it is not as complicated as it might seem.
I am going to make some assumptions here, and give you an example of how this might work. Let's say table1 only has one row, and these are the headers you want in your output file:
select f1 as t1, f2 as t2, f3 as t3, f4 as t4, f5 as t5
from table1
union select t1, t2, t3, t4, '' as t5
from table2
The key factor is that both tables we are merging together in this union query are using the same column names. That's why we use f1 as t1. This makes the f1 column use t1 as its column name (commonly called an alias).
Also notice how in table2, we don't have a t5 column, so we just use an empty string and alias it as t5. This allows the columns to match what we have in table1, even though table2 has no data for this column.
When you view this query, you will notice that the header row is in a random place in the query results. We want to make sure it ends up as the top row in the query, so let's add another column so we can sort the results and force the header to the top row.
select f1 as t1, f2 as t2, f3 as t3, f4 as t4, f5 as t5, 0 as mysort
from table1
union select t1, t2, t3, t4, '' as t5, 1 as mysort
from table2
order by mysort;
Beautiful! Now the header is in the top row! Now we can run our export command, and dump the results into a CSV file:
DoCmd.TransferText acExportDelim,,"qryExample","myfile.csv", False
If you don't want the sort order column in your output file, then simply nest the union query inside another query where the mysort column is not included.
Hope this helps!
