UNION ALL query: "Too Many Fields Defined"
ms-access, sql
Solution
It appears that the number of fields being tracked (limit 255) is counted against ALL parts of the UNION ALL. So 3 x 97 = 291, which is in excess. You could probably create a query as a UNION all of 2 parts, then another query with that and the 3rd part.
Problem
I'm trying to get a UNION of 3 tables, each of which have 97 fields. I've tried the following: ``` select * from table1 union all select * from table2 union all select * from table3 ``` This gives me an error message: ``` Too many fields defined. ``` I also tried explicitly selecting all the field names from the first table (ellipses added for brevity): ``` select [field1],[field2]...[field97] from table1 union all select * from table2 union all select * from table3 ``` It works fine when I only UNION two tables like this: ``` select * from table1 union all select * from table2 ``` I shouldn't end up with more than 97 fields as a result of this query; the two-table UNION only has 97. So why am I getting `Too many fields` with 3 tables? EDIT: As RichardTheKiwi notes below, Access is summing up the field count of each SELECT query in the UNION chain, which means that my 3 tables exceed the 255 field maximum. So instead, I need to write the query like this: ``` select * from table1 union all select * from (select * from table2 union all select * from table3) ``` which works fine.