Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    120
    Thanks
    3
    Thanked 2 Times in 2 Posts
    I am working on revising a membership database in Access 2007. I got this message "Query is too complex". So I said to myself, Aha, let's break this query into pieces, and then re-assemble the results later in a final join.

    However, I got the same error message at about the same logical point in developing the split queries. Of course, the split query depends upon the preceding partial results, so am I improving the situation by splitting the work up? THe split-up query that is now failing has only 6 input fields, and 4 constructed fields, so it is way less complex than some other queries in this database which have worked fine for a long time.

    My question: what causes this message: too many queries? complexity level across all the queries in this database? (It does NOT seem to be the complexity of the single query that I'm working on.)
    Is there some useful away around this problem, or have I exceeded Access' capabilities?

    I have about 80 Select queries in aggregate within this database, 15 tables, 13 Union queries, and 10 reports, if any of that matters!

    Thanks for any help.
    Bob Chapman

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    There are at least a couple of things that can cause that error. One is having a query that becomes too long a SQL string. That seems unlikely based on the description of your problem. The other is an issue with the number of joins involved, and I think 2007 is less capable than older versions. Take a look at this MSKB article for some suggestions on how to get around it.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    113
    Thanks
    3
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by WendellB View Post
    There are at least a couple of things that can cause that error. One is having a query that becomes too long a SQL string. That seems unlikely based on the description of your problem. The other is an issue with the number of joins involved, and I think 2007 is less capable than older versions. Take a look at this MSKB article for some suggestions on how to get around it.
    I've had that issue, going back to Access 2.0, with queries that involved nested joins and WHERE clauses containing SELECT statements which, themselves, contain WHERE clauses.

    As for 2007 being less robust in this respect, that isn't too surprising, since Microsoft would prefer that you move all of your queries into some version or other of SQL Server.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by bobchapman View Post
    Is there some useful away around this problem, or have I exceeded Access' capabilities?
    If all else fails, one workaround is to turn one (or more) of the intermediate stages into either an append or make table query, that writes the data to a temporary table, then base the final query on this temporary table (either in part or whole).
    You then need to either delete the temp table, or delete all the records from it, depending on just how you did it.
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Dec 2009
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It's impossible to provide a single answer to this. I'll give you a simple example of a query that will give this message:
    Suppose 3 tables, T1, T2 and T3. If you need to join T1 to T2 using a LEFT JOIN, and T2 to T3 using a JOIN, then you're going to see your Too Complex message. The solution is usually to LEFT JOIN T1 to T2 and LEFT JOIN T2 to T3.
    Without seeing your queries, and the SQL needed to create the tables involved, I can't really offer anything more tangible.

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Phoenix, Arizona, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have dealt with this error many times before (since Access 97 - learning Access in 1997). I found that the problem is mostly due to criteria in the query(s) that do not evaluate automatically where the value must be retrieved from a form or table lookup. To solve the problem, you should fill in the query parameter box with name of the variable and datatype. I have Access databases(accounting and demographic databases with of hundreds of tables, even more queries, and database size of 500 meg.

    Henry J

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    120
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Thanks to everyone for your responses and ideas. Perhaps I can simplify my system a bit to get around the problem.
    Bob

Posting Permissions

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