Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Query is too complex

    I have a report that is tied to a query that works, meaning when I run the query by itself, it works. When I associate that query with a report it say the query is too complex. This report is a copy of report that works fine. I also copied the query that was attached to the origional query, changed slightly and associated with the new report. Again the query works by itself but when associated with the report, its says it's to complex. Where should I look to fix this problem. I assume its in the report but I don't know where to start. Any ideas? Thanks!

  2. #2
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I would try running the report wizard just to see if it works that way. If it says it is still too complex then I would say the query is probably too good for a report so you might want to create a couple of queries to dumb it down or somthing. Let me know how that turns out.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  3. #3
    Join Date
    Sep 2003
    Posts
    83
    What is the recordsource for the report, is it a statement of sql query or is it referring to a query by queryname. What is sql statement of the query, and the exact error message

  4. #4
    Join Date
    Mar 2004
    Posts
    361
    The record source for the report is a biq query. It refers to the query by name. The exact error message is "Query is too complex" Maybe this will help.

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I have had Query to Complex but always when the query is opened. Perhaps it is some borderline situation you have and the opening of the report isa more complex way of opening the query than is opening the query direct.

    I don't know if this helps but what I do is if the query is too complex and there are a few queries running in series then I make the second last query a MakeTable query and the apply the last query to the table that was made. I use a macro to run the make table query.

    If it was a single query (as opposed to the last one in a series) then I would simpify that query and use the simpler query to a MakeTable than query that table.

    Since you are using it for a Report you not need to be worried about the table "in the middle" preventing any data changes going back to the original table.

    Although I do have a form that runs on a query that has a table between the query and the original table. In that case I have a macro that opens a form based on the original table and for matching ID numbers and a series of SetValue macro actions that send all the data back to the original table.

    All hair pulling stuff

    Mike

  6. #6
    Join Date
    Mar 2004
    Posts
    361
    I made that query a make table query and now it works. Where should I call the macro at. I have this report tied to a switchboard. So one click opens the report. Should I run the marco in the on open property of the report?

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I would make a macro as follows:

    DeleteObject The table was previously made by the MakeTable
    OpenQuery The MakeTable query
    OpenReport
    PrintOut
    Close

    I have had problems at times with a MakeTable query replacing the table from the previous run of the MakeTable query. It has worked as though you were importing a table of the same name an so the result was Newtable1 and then NewTable2 etc. This is on Access 95.

    Alternatively, you can leave the table there and run a Delete Query on the table then an Append Query to the table from your query.

    The macro will nicely run all of them in order.

    Mike
    Last edited by Mike375; 05-13-04 at 18:40.

  8. #8
    Join Date
    Nov 2003
    Location
    Australia
    Posts
    36
    i also had this problem with a large qry -- but you can overcome it by breaking it down in2 several smaller queries

  9. #9
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    YES.

    If the problem query is the last of a series such as

    Query 1 is based on Table1
    Query 2 is based on Query 1
    Query 3 is based on Query 2

    In that case if Query 3 comes up To Complex then you change Query 2 to a MakeTable query and Query 3 is now based on that new table.

    If the query comes up To Complex because of a lot of calculated fields then you reduce the number of calculated fields and again change the query into a MakeTable query. You then query the new table and add in the calculated fields that you removed from the other query.

    But you have to keep in mind that a form now based on the last query will not automatically feed data back to the original table. To get around that I just have a form based on the original table and that form is opened to match ID numbers and then a bunch of set values actions insert the data. If you are worried that someone might not activate that action then you lock the fields on the viewing form and give the person a label or command button that opens a form to match the viewing form and where they can change data. When they close that form then a macro opens a form for the matching record on the original table. A similar thing in principle is down if they want to add a record. In that a case an append query appends that record back to the original table.

    Mike

    Mike

Posting Permissions

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