export multiple tables to one excel worksheet of workbook

[Stevie C]

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?

 

[Ken Snell]

(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>

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).

 

[Ken Snell]

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>

option 1 sounds like the most efficient way of doing it, however i have

hit a brick wall when it comes to building a query that combines the data
from all the tables....

 

[Joe Fallon]

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

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).