how do i resolve the 'system resource exceeded' error?

[Marlene]

I keep getting the "System Resource Exceeded" error message when running a
very complex query. How do I get around this, or resolve it?

Thanks,

Marlene

 

[Jerry Whittle]

http://support.microsoft.com/default.aspx/kb/918814

The above link in for Access 2007 but some of the advice applies to all
versions.

Complex queries are often a sign of a badly normalized database.

 

[John W. Vinson]

Complex queries are often a sign of a badly normalized database.

hey... waitaminit there! <g>

Or a well normalized database with a need for a complex query to pull data
together from multiple tables.

Or both!

John W. Vinson [MVP]

 

[Jerry Whittle]

Very true! I'd thinking 'bad' more in the line of multiple nested IIf
statements; Union queries due to the same data being in more than one field
or table such as a field for each month or table for each year; Left, Right,
Mid, and InStr to pull out data like full names being in the same field.

I recently had a developer request that I put all the data in one table
because it was "too hard" to put joins in SQL statements!

 

[Marlene]

Well thank you very much for such a prompt reply. I had searched through
the forum a little and had found this article but I am at a loss as to how to
make my query any less complex and/or with fewer than 16 joins. I'm at in a
crunch for this information and don't know what the heck to do at this point.
I have "dismantled" the queries and tried to streamline it a little more,
but maybe I'm just a novice because I don't know how to get the information I
need without the joins that are already in place.

Thank you for your help. Merry Christmas!!

 

[John W. Vinson]

I keep getting the "System Resource Exceeded" error message when running a
very complex query. How do I get around this, or resolve it?

Thanks,

Marlene

This may be one of those cases where a maketable query is needed. If this
complex query is built up by combining smaller components, you may be able to
create a MakeTable query combining (say) eight of the sixteen tables into one
non-normalized table, and then join IT to the remaining eight tables. The
temporary table can be deleted when you're done with it; if this becomes
routine you should consider creating such temp tables in a sepearate linked
..mdb file used just for that purpose.

John W. Vinson [MVP]

 

[Jerry Whittle]

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.

However John's suggestion for a Make Table query might be best.