UNION ALL query: "Too Many Fields Defined"

Question

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.

Answer 1

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.

Answer 2

I had two tables with 173 fields each (2 x 173 > 255!). So I had to resort to splitting the tables in half (keeping the primary key in both), before using the UNION statement and reassembling the resulting output tables using a JOIN.

    select u1.*, u2.* 
    from (
      select [field1_PKID],[field2],...,[field110] 
      from table1

      union all

      select [field1_PKID],[field2],...,[field110] 
      from table2
      ) as u1
    inner join (
      select [field1_PKID],[field111],...,[field173] 
      from table1

      union all 

      select [field1_PKID],[field111],...,[field173] 
      from table2
      ) as u2
    on [u1].[field1_PKID] = [u2].[field2_PKID]