Unanswered: Limit on no. of tables in a union query?
I am trying to construct a union query with 46 SELECT statements (this is a database to track employee training needs; there are 46 courses included in it).
The query will not run - message is "System Resources Exceeded". When I cut it back to 39, it works fine.
I tried 2 queries of 23, then a query to merge those, but the result was the same.
Does MS Access have a built-in limit of 40 statements? (I am running Access 2002 under Win '98).
Do I neet to update or customize my Jet DB engine settings?
Is there a workaround for this?
i cannot answer the question about the number of tables in a union, but as for workarounds, my question is, why do you have 46 different tables for 46 different courses? how different can the courses be? i'd've thought there would be one course table and at worst a few different columns that distinguish the different types of courses
so one workaround is to redesign your tables, but i realize how that might have a "ripple effect" on your application code that maintains all the different tables
another workaround is to one union with half the tables, save the results in a temp table, run another union with the other half and also include the temp table in an extra query in the second union