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!
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.
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.
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?
DeleteObject The table was previously made by the MakeTable
OpenQuery The MakeTable query
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.
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.