J
Jake_G
Hello,
In my office, we have a complex Access file which we use to create our
monthly reports that we send to our clients. The file was initially created
two years ago, but since then the company has grown immensely and the file
was updated many times to meet our growing needs. Many of the calculation
that the files does nowadays were not part of the initial design and so they
are done in a very inefficient way. As a result of that, the file contains a
few dozens of queries, in the middle of which there's a long chain of queries,
each based on another query's (or queries') output.
As you can probably guess, we've been having a lot of those "Query Too
Complex" errors. We overcame this problem by adding a few intermediate tables,
which contain the output of some of the complex queries, and then basing the
subsequent queries on those intermediate tables, instead of the queries
themselves.
Because this solution is a temporary one, and in light of the fact that our
company is still growing and we may need to add more functions to this Access
file, my boss decided to build this file from scratch, only this time trying
to make it as efficient as possible and take into consideration the needs
that may arise in the future. This project felt into the hands of yours truly.
Now, I'm sure I can greatly improve this file's efficiency, but I don't think
there's a way to altogether avoid this chain of complex, subsequent queries.
This means that I, most likely, am going to encounter the "Query Too Complex"
error, times and again.
My questions are as following:
1. What are the general guidelines in avoiding the dreaded "Query Too
Complex" error?
2. Should I strive to write one huge query or should I just break it down to
a large number of very simple queries instead?
3. What's the best way to arrange the initial tables?
4. Should I avoid having too many columns in a query?
5. Does the number of lines in an input table/query has an effect on the
probability of getting the "Query Too Complex" error?
6. Would a significantly more powerful PC help avoid the error?
Thanks in advance,
Jake.
In my office, we have a complex Access file which we use to create our
monthly reports that we send to our clients. The file was initially created
two years ago, but since then the company has grown immensely and the file
was updated many times to meet our growing needs. Many of the calculation
that the files does nowadays were not part of the initial design and so they
are done in a very inefficient way. As a result of that, the file contains a
few dozens of queries, in the middle of which there's a long chain of queries,
each based on another query's (or queries') output.
As you can probably guess, we've been having a lot of those "Query Too
Complex" errors. We overcame this problem by adding a few intermediate tables,
which contain the output of some of the complex queries, and then basing the
subsequent queries on those intermediate tables, instead of the queries
themselves.
Because this solution is a temporary one, and in light of the fact that our
company is still growing and we may need to add more functions to this Access
file, my boss decided to build this file from scratch, only this time trying
to make it as efficient as possible and take into consideration the needs
that may arise in the future. This project felt into the hands of yours truly.
Now, I'm sure I can greatly improve this file's efficiency, but I don't think
there's a way to altogether avoid this chain of complex, subsequent queries.
This means that I, most likely, am going to encounter the "Query Too Complex"
error, times and again.
My questions are as following:
1. What are the general guidelines in avoiding the dreaded "Query Too
Complex" error?
2. Should I strive to write one huge query or should I just break it down to
a large number of very simple queries instead?
3. What's the best way to arrange the initial tables?
4. Should I avoid having too many columns in a query?
5. Does the number of lines in an input table/query has an effect on the
probability of getting the "Query Too Complex" error?
6. Would a significantly more powerful PC help avoid the error?
Thanks in advance,
Jake.