Query works, make table based on it "too complex"

Question

I have a query.  A union query in three parts.

This query works perfectly fine.  I can open it, I can export it to Excel, with no problems whatsoever.

I create a make table query, with no criteria, no calculations, NOTHING except a simple list of each field in the original query, to a table in the existing database.  In short, dump this query to a table.

I Instantly get "Query too complex."

The results of this query are 13 fields,  and 2428 rows.

Creating a table does not make it more complex.  What the heck is going on here?

Answer 1

Build the table, change the query to an append query,

Same answer: Query too complex.

Answer 2

Must be something about your query that works ok for just an in memory copy, but fails when trying to save the data in a table.  I have never seen this happen so I suspect it will take a fair amount of digging to figure out what it is about the query that's causing the problem.

I would start by removing the unions and add them back one at a time until it fails.  Then at least you have narrowed the problem down a little.

Answer 3

Build the table, change the query to an append query,

Same answer: Query too complex.

Answer 4

I don't have an answer to that, but, IMO, SELECT INTO (Make Table) queries are in general an artificial concept.  The execution of  this kind of query requires the creation of a new table (no big deal) and all the fields (not too difficult) and their properties (can be difficult and potentially impossible).  With a union type query, where the fields can come from different tables and have different property settings (required, validation rules, data type, etc), resolving conflicts in the property settings can devolve into a matter of guessing.  And when the guessing doesn't work well enough, the bail out is to say it's too complex.

The real purpose of this reply is to argue against using a make table query in just about any situation, especially when it won't work.  Instead, pre create a scratch table with the desired fields and properties and then empty the table using a DELETE query and repopulate the table with an append query.  Ideally, the scratch table, however it's created, should be in a temporary database to avoid bloat and its consequent issues.

Answer 5

I can't answer the why, but since this is a stored query and the structure is set, why not delete the records and append. I would only use Make Table if the structure was unknown.