Create a Single Consolidated Table from Multiple Linked XLS Tables

[Manerlao]

Hi all,

I have been trying to consolidate a number of linked tables together in order to create a single consolidated table from data sources.
If anyone can help, this would be really great! (I am quite new to MS Access)
I have been able to link almost all of my tables, but I keep getting an error: "Query is too complex" after I try and append/union the last few tables.
I have been trying for the whole weekend without any luck... thank you.

Structure:
I have about 30 groups of tables in MS Access each containing 20 linked XLS tables in them (Table 1 to Table 20). So in total, there are around 600 linked tables.
All tables are in the same structure/format. I.e. All table 1 are the same structure, all table 2 are the same structure...

Objective:
Consolidate all linked tables of one kind (i.e. put all the Table 1's across all 30 groups into a single table).
So far I have been able to do this via a union in SQL.

Status:

When I select "Design View" the code I have is the following:

Select * from Table1_y
UNION ALL select * from
(Select * from Table1_x
UNION ALL select * from
(Select * from Table1_w
UNION ALL select * from
(Select * from Table1_v
UNION ALL select * from
(Select * from Table1_u
UNION ALL select * from
(Select * from Table1_t
UNION ALL select * from
(Select * from Table1_s
UNION ALL select * from
(Select * from Table1_r
UNION ALL select * from
(Select * from Table1_q
UNION ALL select * from
(Select * from Table1_p
UNION ALL select * from
(Select * from Table1_o
UNION ALL select * from
(Select * from Table1_n
UNION ALL select * from
(Select * from Table1_m
UNION ALL select * from
(Select * from Table1_l
UNION ALL select * from
(Select * from Table1_k
UNION ALL select * from
(Select * from Table1_j
UNION ALL select * from
(Select * from Table1_i
UNION ALL select * from
(Select * from Table1_h
UNION ALL select * from
(Select * from Table1_g
UNION ALL select * from
(Select * from Table1_f
UNION ALL select * from
(select * from Table1_e
UNION ALL select * from
(select * from Table1_d
UNION ALL select * from
(select * from Table1_c
UNION ALL select * from
(select * from Table1_b
UNION ALL
select * from Table1_a)))))))))))))))))))))));

It works well, but as soon as start to add more tables than the above (I.e. I add/union "Table z"), it fails with the error: "Query is too complex"

Does anyone know why? Could anyone kindly help me with this? I would really appreciate it, it's been taking me so long to figure out what the issue might be.
If you need further details from me, please let me know.

Thank you and best,
Manerlao

 

[xenou]

it might work to start a brand new query:

select * from Table_z
UNION ALL
select * from Query1;

where Query1 is your query above.

 

[Manerlao]

Hi Xenou,
Great, let me try this - thank you
I'll let you know shortly

 

[Manerlao]

Hi Xenou, I just tried, I get the same error: "Query is too complex".

Do you maybe know if there is any other way to join all these linked tables together into one consolidated table?

Thank you and regards,
Manerlao

 

[xenou]

I would in fact prefer to just insert all the records into a real table than doing a huge union query. That would simply be a whole bunch of insert queries.

 

[Manerlao]

Ok, I'll try this! Thank you for the advice here.

 

[Manerlao]

I may try and "Append" all of the linked tables into a new table then? Is it quite straight forward to do this?
I was trying and it seems I can only do this 1 by 1.

Please let me know if you have any other advice, this would be really appreciated. Thank you, Xenou.

 

[JonXL]

Why so many parentheses?

You don't need those for UNION queries. I'm wondering if you get rid of them whether that will solve your problem...

 

[Micron]

You don't nest/stack UNION queries?
Select * from Table1_y
UNION ALL
select * from Table1_x
UNION ALL
select * from ...

You might mess things up if you have PK / FK relationships between these tables. If table1.field1 is an autonumber PK and there are related FK values in other tables you may need these values, yet they'll likely be duplicated as they get amalgamated into one table.

 

[Manerlao]

Why so many parentheses?

You don't need those for UNION queries. I'm wondering if you get rid of them whether that will solve your problem...

Thank you, Jon and sorry for the late reply!
Hmmm... Actually I tried without the parenthesis and it didn't seem to work. Maybe I'll try again then.

If you happen to know the structure I should put this in, it would be really useful. Thank you again for the help here.