Union query limit?

No specific limit per se.  I have one union query with 22 unions ... runs with no issue.  No clue what would be causing your issue.

mx

Please post the SQL that gives the error.

DatabaseMX has answered my specific question which is there is no union limit.  Clearly my problem must be SQL syntax.  The following example query would fail.

select "a" from T
UNION
select "b" from T
UNION
select "c" from T

I'm going to increase points so I can get a solution to this problem.  DatabaseMX has already earned 50 points for answering there is no UNION limit.

My problem comes from my query.

If I typed:

select "a" from Table1
UNION
select "b" from Table1

I will get:
a
b

as the result

But if I throw another UNION in there,

select "a" from Table1
UNION
select "b" from Table1
UNION
select "c" from Table1

I get that ODBC error message. What is wrong with my syntax?

Here is the general format for a UNION query:

SELECT Table1.* FROM Table1
UNION SELECT Table2.* FROM Table2
UNION SELECT Table3.* FROM Table3
UNION SELECT Table4.* FROM Table4

and so on.

Note there are no double quotes ... and each table is a different table.

Also, the number of columns must be the same for each table (or query) data types for each column - in order - must match.  The names can be different.

mx

I was just giving an example and the quotes are just for selecting a bogus piece of data.  All of the individual tables will come from the same query with a difference in the select clause for each of them.  The difference in the select clause is a name of a column (that's why I'm using quotes).  The datatypes and number of items in the select are the same.

I'm basically taking columns and transposing them into a single column and union'ing those queries together so all of the results are put in a single long vertical listbox instead of having a super wide result.

Sorry ... I don't know why you would be getting the ODBC error message.

Did you post this in the mySQL zone also


mx

Is there a way to print all of my query to the screen?  If I could do that, then I could examine my query more closely or paste it hear.  I use MsgBox but there's a character limit.

Well, you could use Debug.Print  <some variable> ... and see the results in the Immediate Window.  I believe there is a limit there also, but more than the Message Box.

By 'print all ...' ... not sure what you mean?  results? SQL?


mx

print the query string.  It's very large after many unions.

has anyone tried the following in Access?

substitute YourTable with any table you'd like:

select "a" from YourTable
union
select "b" from YourTable

I would expect this to work with the result:

a
b

Now, try the same thing by adding another union to the query

select "a" from YourTable
union
select "b" from YourTable
union
select "c" from YourTable

This simple little example illustrates my problem.   My query will fail whether it's this small query or if it's a large, complex query.

The error I get is:
"ODBC--call failed
[MySQL][ODBC 3.51 Driver][mysqld-5.0.26]You have an error in my SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT 'b' FROM `YourTable`)) UNION (SELECT 'c' FROM `YourTable`)' at line 1(#1064)

Now, I can paste my above queries directly in the MySQL query browser and it works.  Somehow this is related to Access.  Can anyone reproduce this?

It doesn't fail within an Access MDB ... so I would say the issue is related to Access interfacing with MySQL.

mx

Also, based on the above info - using VBA to create your query (embedded SQL) may also work.
One final note, Access queries are bound to these limitations:
http://www.databasedev.co.uk/access_specifications.html#content3
UNION statements fall under the "nested queries" category.
If you have more than 50 UNION statements, the query will fail with a "Query is too complex" error.

Same problem here, resolved by first making a union query with two tables, after that make a new union between the created unionquery and a new table. Works somehow, although the underlying sql is essentially the same. Good luck.