export multiple tables to one excel worksheet of workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have an access 2002 database containing multiple tables (of the same structure, each with different information) that i would like to export to one excel worksheet (or multiple worksheets in one workbook)

any thoughts?
 
(1) Write a query that combines all the data from the different tables, and
then export that query.

(2) Use Automation via VBA code to open recordsets on the tables and write
the fields' contents directly into the EXCEL cells.

Post back with more info and we'll see which option may be better for you.

--
Ken Snell
<MS ACCESS MVP>

Stevie C said:
i have an access 2002 database containing multiple tables (of the same
Click to expand...
structure, each with different information) that i would like to export to
one excel worksheet (or multiple worksheets in one workbook).
 
Only if you can give me some info about the tables' structures and contents
and how you want the final output to look!
< g >

--
Ken Snell
<MS ACCESS MVP>

Stevie C said:
option 1 sounds like the most efficient way of doing it, however i have
Click to expand...
hit a brick wall when it comes to building a query that combines the data
from all the tables....
 
This is not a good design:
multiple tables (of the same structure, each with different information)

However, it is a common error so don't feel bad.

The solution to your query problem is to use the UNION operator.
e.g.
If you have 3 tables, you write 3 queries named qry1, qry2 and qry3.
Each query is correct all by itself and gives you what you want.
NOTE: they all have the same number of fields (in the same order if you want
it to make sense.)

Now you write qry4 like this:
qry1
UNION
qry2
UNION
qry3

You have to be in SQL view to do this. And you can't go back to Design view.

--
Joe Fallon
Access MVP



Stevie C said:
i have an access 2002 database containing multiple tables (of the same
Click to expand...
structure, each with different information) that i would like to export to
one excel worksheet (or multiple worksheets in one workbook).
 
Back
Top