Maximum # of unions in access query?

[aaronglover]

Hey Guys,

Is there a maximum number of union queries you can have in an 07 access database? When I have 51 or more unions in my query I get an error "Query is too complex" Anyone know of any solutions?

My problem is I am working with a dataset that has a column for each time interval (96 in total) and I am trying to convert it so there is a row per interval instead. It is all fine until I add union # 51 and then it craps out.

Thanks

 

[ajetrumpet]

Hey Guys,

Is there a maximum number of union queries you can have in an 07 access database? When I have 51 or more unions in my query I get an error "Query is too complex" Anyone know of any solutions?

My problem is I am working with a dataset that has a column for each time interval (96 in total) and I am trying to convert it so there is a row per interval instead. It is all fine until I add union # 51 and then it craps out.

Thanks

i've run into the complex error too sometimes. acc has limits obviously, and this is one of them. you're overloading it i think. but then again, why would there ever be a need for 51 of these?

 

[boblarson]

You might need to "convert" some fields first and the the others.

 

[Atomic Shrimp]

I agree. Assuming this is a project to try to normalise your data (and it sounds like it is), you might have to do it in a number of smaller steps - if it's a one-time conversion of a legacy database into something more robust, then this is one of those occasions when it's quite OK to store query results in intermediate tables, while you're stitching it all together.

When you get to the end, you can always export the final versions of the tables into a fresh database and archive the one that contains all your working.