Append Tables

[Jeff]

Hi,

I have 4 "select" queries that create 4 tables. The tables have different
information but are the same dimensions, so I want to append the tables and
then export to csv.

The only way I know how to do this is to create 4 tables with a "make table"
query and then append these 4 and then export. But is there a way to do this
without creating another 4 tables - this takes up more space - but just
automatically combine with another query and then export the query to a csv
file?

Thanks

 

[Jeff Boyce]

Jeff

Rather than recreating a "make table" each time, why not create a
"receptacle" table once, with the field-types you'll need. Then, rather
than creating 4 more tables each time, those 4 queries can be append
queries, appending to your more permanent receptacle table.

You'll need to add a "clear all records" (i.e., delete) query to run before
the 4 append queries. And if you want to automate this, including the
export, you could create a macro that:
* deletes existing records from the receptacle table
* appends info1
* appends info 2
* ...
* exports as csv

Is that what you were looking to do?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Jeff]

Jeff -

Your idea is better then creating 4 tables and appending all 4. In my
database I only have 1 main "data table" and when I do a query and get the
data I export the query to a csv. Because this minimizes the size of the
database. I was hoping that there was a way to use a "Select" query to
combine the other "select queries" and then export the "Appended" select
query.

But your method is much improved over what I was doing.

Thanks for your help!

 

[Pat Hartman]

What you are looking for is a union query.
Select * from qry1
Union Select * from qry2
Union Select * from qry3
Union Select * from qry4;

Then export the union query.

 

[Jeff]

I went into "sql" view and typed

SELECT * from qry1
Union Select * from qry2;

But got an error - "Syntax Error in From clause"

 

[Jeff]

Actually I had a typo - so it works!\

Thanks for your help this makes my job so much easier