Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    Bolton, Ontario, Canada
    Posts
    4

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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


    rudy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •