"append query" issues

[Stevie C]

i am trying to develop an "append" query, that combines the data from 70 tables (each of the same structure but with different data) into one table. i will then use this aggregated table as the recordsource for a subreport in order to take advantage of the reports parent/child link facilit

i keep getting the error message "duplicate output destination" whenever i run the query

can somebody guide me through? thanks

 

[Nikos Yannacopoulos]

Stevie,

What you need to achieve what you want is not an append query, it's a union
query (look it up in Access help to see how to do it).

Points to think about:
1. If you only need this for reporting, there is no reason to duplicate the
data in a new table; use the query per se as the recordsource.
2. Ask yourself why you really need 70 different tables with the same
structure, when you could have just one with one extra field to
differenciate whatever it is you are differentiating by having multiple
tables. If it's not too late in your project, it probably makes good sense
to change it. It would make your design a lot more cohesive and easy to
develop and maintain (for instance, you would not have this question
now...).

HTH,
Nikos

i am trying to develop an "append" query, that combines the data from 70

tables (each of the same structure but with different data) into one table.
i will then use this aggregated table as the recordsource for a subreport in
order to take advantage of the reports parent/child link facility

 

[Stevie C]

thanks nikos - the union query worked a treat and was very straightforward!

only problem is, as soon as i got the method correct and added the remainder of my tables to the query, it now only returns a "Query too complex" error message and returns no data.

i'm thinking that maybe i have to run a series of smaller queries, and dump the contents into a temporary table? only problem is, each of the tables has no primary key - any thoughts?

 

[Nikos Yannacopoulos]

Stevie,

Temp table(s) might do the trick but, in my humble opinion, it's a half
measure. I would still urge you to revisit your db desing and merge the 70
tables into 1 with an extra field to hold whatever information is now "held"
in the table separation itself.

Nikos

thanks nikos - the union query worked a treat and was very straightforward!

only problem is, as soon as i got the method correct and added the

remainder of my tables to the query, it now only returns a "Query too
complex" error message and returns no data.

i'm thinking that maybe i have to run a series of smaller queries, and

dump the contents into a temporary table? only problem is, each of the
tables has no primary key - any thoughts?

 

[BJMLaJolla]

I have the same problem. I have 70 tables that I want into one table. All tables have the same structure with different content. I tried an Append Query but got "multiple" destination message. I am stuck with the 70 tables as my source. My question is how do I merge them into one table.

 

[John Spencer (MVP)]

Normally, you would use an append query on one table at a time.

INSERT INTO TargetTable (FieldA, FieldB, FieldC)
SELECT S.FieldA, S.FieldB, S.FieldC
FROM Source1 As S

Repeat the above for each of the 70 tables.

You can use a union query to combine multiple tables, BUT you will run into a
limit on the number of tables/fields that you can include before you get all 70
tables into your union query.

If you don't understand this, then can you post back with the SQL of the query
that is failing?