Query too complex - Acces 2007 (SP3)

[qsdf]

A client is getting this error when he is trying to execute this query:

TABLE [table1] UNION ALL TALBE [table2]

I'm not sure if this query is right. If we use UNION (without all) the query works just fine.

SELECT * FROM table1 UNIOAN ALL SELECT * FROM table2 the same as the previous querty? Because that one is working well.

Thank you very much.

 

[KenHigg]

My guess is the columns don't line up.

 

[qsdf]

Re: Query too complex - Access 2007 (SP3)

But it worked in previous versions of Access (without SP3).

TABLE [table1] UNION ALL TALBE [table2] < Doesn't work.
TABLE [table1] UNION TALBE [table2] < Works.

SELECT * FROM table1 UNIOAN ALL SELECT * FROM table2 < Works, but I doesn't know this has the same result?

 

[KenHigg]

Are all the column data types the same?

Here is a good read on the topic:

Link

 

[qsdf]

Are all the column data types the same?

Yes, they are. (Also, worked before SP3, so the query must be right?)

 

[KenHigg]

Is there anything else going on like filters, etc?

 

[qsdf]

Is there anything else going on like filters, etc?

Not that I am aware of, sorry.

TABLE [table1] UNION ALL TALBE [table2]
SELECT * FROM table1 UNIOAN ALL SELECT * FROM table2

Are those 2 query's the same?

 

[jonathanchye]

From experience this could be caused by a null value in the union joins... check that none of your tables are returning null.

 

[qsdf]

From experience this could be caused by a null value in the union joins... check that none of your tables are returning null.

What is the best way to check this?

Thank you very much

 

[KenHigg]

From experience this could be caused by a null value in the union joins... check that none of your tables are returning null.

Nice catch - That may explain some issues I had has well

 

[jonathanchye]

What is the best way to check this?

Thank you very much

Well normally I'd just run a query to return the results of the tables individually and visually check if there's not null fields. You can write a custom query too if you have a big table

 

[jonathanchye]

Nice catch - That may explain some issues I had has well

Glad I am able to help

 

[qsdf]

Well normally I'd just run a query to return the results of the tables individually and visually check if there's not null fields. You can write a custom query too if you have a big table

Ok, thank you very much. I'm going to try this ASAP.

 

[qsdf]

Ok, thank you very much. I'm going to try this ASAP.

He is using this query on 2 different sets of tables. On the first set is a NULL value, on the second set there isn't a NULL value.

Hmm...