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

 

[Manerlao]

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.

Hi Micron,

Actually I am very new to MS Access and SQL, so your advice is really helpful, thank you for this!
I will try to explain the details more clearly for you and the community so a resolution is more easily found. Please bear with me and thank you.

I'm not sure what PK and FK mean (Sorry ), maybe you mean public key? If so, I don't think I have a PK, since I only have "linked" XLS files in the Database.
All the raw data sits within 20-30 separate XLS workbooks with 20 sheets each -> linked into MS Access -> all sheets have the same structure so I am able to union all of them easily from MS Access.
except for the error I am having above: "Query is too complex" When I union too many.

In other words, my XLS files contain the raw data, and I am using MS to just link to those XLS files and aggregate all the files together for each table type.

In any case, I don't really need to define the public key yet ( or maybe I'm wrong?) as my objective is to just union all the linked tables of the same type, so I can simply have that as an aggregated table.
I aim to define the PK as 3 fields: Date, Data Type, Parent, so these will all be unique cases. But again, I am using linked tables, so I will come back to this later.

I.e. My logic and structure within MS Access:
Table1 = Sheet1 in linked XLS file 1 UNION TO Table1 = Sheet1 in linked XLS file 2 UNION TO Table1 = Sheet1 in linked XLS file 3 ...
Table2 = Sheet2 in linked XLS file 1 UNION TO Table2 = Sheet2 in linked XLS file 2 UNION TO Table1 = Sheet1 in linked XLS file 3 ...
Table3= Sheet3 in linked XLS file 1 UNION TO Table3 = Sheet3 in linked XLS file 2 UNION TO Table1 = Sheet1 in linked XLS file 3 ...
.....................
TableN= SheetN in linked XLS file 1 UNION TO TableN = SheetN in linked XLS file 2 UNION TO Table1 = Sheet1 in linked XLS file 3 ...
.....................
Table20= Sheet20 in linked XLS file 1 UNION TO Table20 = Sheet20 in linked XLS file 2 UNION TO Table1 = Sheet1 in linked XLS file 3 ...

So I would like all Table1 across all XLS in a single aggregated structure for the 20-30 linked XLS files.
Same for all the other tables.

I'll try your idea in conjunction with JonXL's. Let me see what I can do.

Please challenge me on this. My logic may be entirely incorrect? Or maybe you think I should do something completely different? Please be frank and thank you for the advice to you and everyone else in the community!

Kind regards,
Maneralo

 

[Manerlao]

Dear Xenou, JonXL and Micron,

I removed the parenthesis, and the union works quite well now!
I don't get any "Query is too complex".


I extend my sincere thank you to you three for the kind help here.
Hopefully, I can build this further with your guidance!

We can close this thread now.

Best regards,
Manerlao

 

[xenou]

good catch!

 

[Manerlao]

Hi all,

I actually encountered just one issue with one of the groups of tables I am trying to "union".
This issue is still related to this threat, so I don't need to make a new one.

My problem:
Table7 in MS Access corresponds to Sheet7 in the Linked XLS file across the 20 workbooks: Table7 has about 200 fields (200 columns in excel), which is less than the 255 limits from MS access. So it is fine.

However, when I union them via the following code:

select * from Table1_XLS_1
UNION ALL
select * from Table1_XLS_2
UNION ALL
select * from Table1_XLS_3

...

UNION ALL
select * from Table1_XLS_30

I receive an error: "Too many fields defined"

If I union them like this with parenthesis and the additional code:

select * from Table1_XLS_1
UNION ALL select * from
(select * from Table1_XLS_2
UNION ALL select * from
(select * from Table1_XLS_3
UNION ALL
select * from Table1_XLS_4))

It works. But only up to a limited number of unions. So I can't include all of the unions across my remaining linked tables.

So I am quite confused about what's happening here.

I don't suppose anyone has an idea about this?
Please if someone could very kindly help me with this issue - thank you very much.

Kind regards,
Manerlao

 

[xenou]

Do all of your tables have the same 200 fields? This error only really makes sense of one of the tables has more than 200 fields (or less than 200 fields).

 

[Micron]

There are a few limitations with Access queries; one of them is character size. Could you be exceeding the 64Kb size limit with 200 fields and 20 - 30 sheets like that? Only you might know that.
Consider UNION for about half of them, create an append query that uses that UNION to append to a temp table (temp records, not a table that you repeatedly delete and recreate) and then append the other half the same way with a second UNION.

PK is primary key.

 

[Manerlao]

Dear Xenou and Micron,

Let me try your ideas out first. Thank you for these suggestions.
I will let you know how I get on.

 

[Manerlao]

Do all of your tables have the same 200 fields? This error only really makes sense of one of the tables has more than 200 fields (or less than 200 fields).

Hi Xenou, yes, all my linked XLS sheet1's have the same 200 fields. Therefore, the table1's created from these linked XLS file shave the same 200 fields.

The fact that it works for the code with the parenthesis is quite strange.

For the code without parenthesis, I am able to capture about 25 tables all to be successfully "Unioned".

Kind regards,
Manerlao

 

[xenou]

I don't really know what your purpose is but as I mentioned you can either
1) work out the kinks using the longest Union queries possible (and assume that you cannot do all in one union query - so maybe about 20 tables, then),
2) aggregate the data into a single table that you can then query as a normal table.

Here is an example of a routine to consolidate all the data into one table. But of course the simple non-technical way is just create the append queries you need (if you have 30 source tables, then you need 30 queries, or maybe 31 if you have one more at the start to clear out the existing data before repopulating the aggregate table with current data.

Note that in this case all the tables called Table1_XLS_? , where ? is a number , are dumped into a single table called Table1_XLS_Aggregate. I added one field to indicate where the original data came from (out of habit) but you may not even need that.

Sub Update_Aggregate_Table()

Dim col As New VBA.Collection
Dim i As Long

Call Get_XLS_Tables(col)
If (col.Count > 1) Then
   
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("DELETE * FROM Table1_XLS_Aggregate;")
   
    For i = 1 To col.Count
        Debug.Print col.Item(i)
        DoCmd.RunSQL (col.Item(i))
    Next i
    DoCmd.SetWarnings True

End If

End Sub

Sub Get_XLS_Tables(ByRef col As VBA.Collection)

Dim s As String
Dim counter As Long
Dim key As String
Dim rs As Recordset

s = ""
s = s & " select MSysObjects.name"
s = s & " from MSysObjects"
s = s & " where"
s = s & " MSysObjects.type In (1,4,6)"
s = s & " and MSysObjects.name not like '~*'"
s = s & " and MSysObjects.name not like 'MSys*'"
s = s & " order by MSysObjects.name"

Set rs = CurrentDb.OpenRecordset(s)

If Not rs.EOF Then
    Do While Not rs.EOF
        If LCase(Left(rs.Fields(0).Value, 10)) = "table1_xls" Then
            If LCase(rs.Fields(0).Value) <> "Table1_XLS_Aggregate" Then
                counter = counter + 1
                key = "K" + CStr(counter) '//An arbitrary key for this item
                col.Add "Insert Into Table1_XLS_Aggregate Select '" & rs.Fields(0).Value & "' as TableSource, * From " & rs.Fields(0).Value & ";", key
            End If
        End If
        rs.MoveNext
    Loop
End If

Set rs = Nothing

End Sub

 

[Manerlao]

I don't really know what your purpose is but as I mentioned you can either
1) work out the kinks using the longest Union queries possible (and assume that you cannot do all in one union query - so maybe about 20 tables, then),
2) aggregate the data into a single table that you can then query as a normal table.

Here is an example of a routine to consolidate all the data into one table. But of course the simple non-technical way is just create the append queries you need (if you have 30 source tables, then you need 30 queries, or maybe 31 if you have one more at the start to clear out the existing data before repopulating the aggregate table with current data.

Note that in this case all the tables called Table1_XLS_? , where ? is a number , are dumped into a single table called Table1_XLS_Aggregate. I added one field to indicate where the original data came from (out of habit) but you may not even need that.

Sub Update_Aggregate_Table()

Dim col As New VBA.Collection
Dim i As Long

Call Get_XLS_Tables(col)
If (col.Count > 1) Then
  
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("DELETE * FROM Table1_XLS_Aggregate;")
  
    For i = 1 To col.Count
        Debug.Print col.Item(i)
        DoCmd.RunSQL (col.Item(i))
    Next i
    DoCmd.SetWarnings True

End If

End Sub

Sub Get_XLS_Tables(ByRef col As VBA.Collection)

Dim s As String
Dim counter As Long
Dim key As String
Dim rs As Recordset

s = ""
s = s & " select MSysObjects.name"
s = s & " from MSysObjects"
s = s & " where"
s = s & " MSysObjects.type In (1,4,6)"
s = s & " and MSysObjects.name not like '~*'"
s = s & " and MSysObjects.name not like 'MSys*'"
s = s & " order by MSysObjects.name"

Set rs = CurrentDb.OpenRecordset(s)

If Not rs.EOF Then
    Do While Not rs.EOF
        If LCase(Left(rs.Fields(0).Value, 10)) = "table1_xls" Then
            If LCase(rs.Fields(0).Value) <> "Table1_XLS_Aggregate" Then
                counter = counter + 1
                key = "K" + CStr(counter) '//An arbitrary key for this item
                col.Add "Insert Into Table1_XLS_Aggregate Select '" & rs.Fields(0).Value & "' as TableSource, * From " & rs.Fields(0).Value & ";", key
            End If
        End If
        rs.MoveNext
    Loop
End If

Set rs = Nothing

End Sub

Hi Xenou!

Thank you for this explanation and code. I will go ahead and see if your suggestion works for me. I will be in touch soon while I sort out this issue!
I'll let you know any updates soon!

Best regards and I appreciate the clear guidance you have provided.
Manerlao