Query too complex

[David McKnight]

Is there any general guildlines for preventing getting this message, granting
that I do have a very complex query?

I have a half dozen or so queries in a series (one dependent on the
previous and so on) - My only cwork around I have have now is having to copy
the results of a intermediate query to a tabel and then referencing that
table for the subsquent query.

 

[Jeff Boyce]

David

Do you only get this message at the end of your series? Is there one
particular query that results in this message?

 

[David McKnight]

At end of series. Briefly described:

Query 1

Finds all Games played in November from table [games]

table [games] fields = [date][year][visitor][home] [margin]

Query 2

Uses Query 1 link to table [ranking] and table [ranking_1] + unlinked table
[coef]

Make [residual]calculation by: [margin] -
(([rankng.home]-[ranking_1.visitor])* coef)

Query 3

Avg of [residual] for visitors

Query 4

Avg of [residual] for home

Query 5

Union of Query 3(*-1) & Query 4

Query 6

Avg of Query 5 for each team, since each team may have a results for home
games and as a visitor

Query 7

Query 6 avg residual value/2 + table [ranking] = [November avg performance]

It is query 7 where the "Query too complex" error occurs.


David McKnight

 

[Jeff Boyce]

David

Don't know if it's applicable in your situation, but I noticed that you're
using Average.

It looks like your queries 3, 4, & 5 might be also handled with a single
Totals query. (but then, I'm not there <g>).

Good luck

Jeff Boyce
<Office/Access MVP>

At end of series. Briefly described:

Query 1

Finds all Games played in November from table [games]

table [games] fields = [date][year][visitor][home] [margin]

Query 2

Uses Query 1 link to table [ranking] and table [ranking_1] + unlinked table
[coef]

Make [residual]calculation by: [margin] -
(([rankng.home]-[ranking_1.visitor])* coef)

Query 3

Avg of [residual] for visitors

Query 4

Avg of [residual] for home

Query 5

Union of Query 3(*-1) & Query 4

Query 6

Avg of Query 5 for each team, since each team may have a results for home
games and as a visitor

Query 7

Query 6 avg residual value/2 + table [ranking] = [November avg performance]

It is query 7 where the "Query too complex" error occurs.


David McKnight

David

Do you only get this message at the end of your series? Is there one
particular query that results in this message?

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

to
copy

 

[David McKnight]

I do need an average value (or keep track of count and divide by later),
however, my general questions are "what are the limitation of building a
series of queries?", "how the limits determined - is it performance of
machine dependent or number of calculation required to come up with an
answer"? "Is there general tricks in getting around these limitation or
designing to prevent a too complex error message?. my current work around is
to create intermediate tables so data can be written to at the point were
queries are getting to complex, thisseems clumsy - is it a recommended work
around? "Would migrating to mySQL or SQL be a solution?"


regards
--
David McKnight

David

Don't know if it's applicable in your situation, but I noticed that you're
using Average.

It looks like your queries 3, 4, & 5 might be also handled with a single
Totals query. (but then, I'm not there <g>).

Good luck

Jeff Boyce
<Office/Access MVP>

At end of series. Briefly described:

Query 1

Finds all Games played in November from table [games]

table [games] fields = [date][year][visitor][home] [margin]

Query 2

Uses Query 1 link to table [ranking] and table [ranking_1] + unlinked table
[coef]

Make [residual]calculation by: [margin] -
(([rankng.home]-[ranking_1.visitor])* coef)

Query 3

Avg of [residual] for visitors

Query 4

Avg of [residual] for home

Query 5

Union of Query 3(*-1) & Query 4

Query 6

Avg of Query 5 for each team, since each team may have a results for home
games and as a visitor

Query 7

Query 6 avg residual value/2 + table [ranking] = [November avg performance]

It is query 7 where the "Query too complex" error occurs.


David McKnight

David

Do you only get this message at the end of your series? Is there one
particular query that results in this message?

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

Is there any general guildlines for preventing getting this message,
granting
that I do have a very complex query?

I have a half dozen or so queries in a series (one dependent on the
previous and so on) - My only cwork around I have have now is having to
copy
the results of a intermediate query to a tabel and then referencing that
table for the subsquent query.

 

[Jeff Boyce]

David

This is one person's impression only -- take it with a block of salt.

I've "chained" together quite a few (?a dozen?) queries without issue. I'm
not aware of any hard & fast rules (although you might want to check Access
HELP for "specifications") about the number of queries.

I do recall seeing an absolute limit on the number of characters in the
(underlying) SQL statement. But that wouldn't be the first place I would be
checking if I were getting a "too complex" error message.

Moving your data to MySQL or SQL-Server probably won't make any difference
in how the Access front-end processes the query, but you would have the
opportunity to create a stored procedure or user-defined function instead of
using your query.

Do you really have the resources (time, $$, servers, specialists) it will
take to bring a full-blown sql server into your organization? Do you really
have the time to rework your application(s) to take best advantage of the
new back-end?

I wouldn't be looking there first, either.

--
Regards

Jeff Boyce
<Office/Access MVP>

I do need an average value (or keep track of count and divide by later),
however, my general questions are "what are the limitation of building a
series of queries?", "how the limits determined - is it performance of
machine dependent or number of calculation required to come up with an
answer"? "Is there general tricks in getting around these limitation or
designing to prevent a too complex error message?. my current work around is
to create intermediate tables so data can be written to at the point were
queries are getting to complex, thisseems clumsy - is it a recommended work
around? "Would migrating to mySQL or SQL be a solution?"


regards
--
David McKnight

David

Don't know if it's applicable in your situation, but I noticed that you're
using Average.

It looks like your queries 3, 4, & 5 might be also handled with a single
Totals query. (but then, I'm not there <g>).

Good luck

Jeff Boyce
<Office/Access MVP>

At end of series. Briefly described:

Query 1

Finds all Games played in November from table [games]

table [games] fields = [date][year][visitor][home] [margin]

Query 2

Uses Query 1 link to table [ranking] and table [ranking_1] + unlinked table
[coef]

Make [residual]calculation by: [margin] -
(([rankng.home]-[ranking_1.visitor])* coef)

Query 3

Avg of [residual] for visitors

Query 4

Avg of [residual] for home

Query 5

Union of Query 3(*-1) & Query 4

Query 6

Avg of Query 5 for each team, since each team may have a results for home
games and as a visitor

Query 7

Query 6 avg residual value/2 + table [ranking] = [November avg performance]

It is query 7 where the "Query too complex" error occurs.


David McKnight


:

David

Do you only get this message at the end of your series? Is there one
particular query that results in this message?

--
More info, please ...

Jeff Boyce
<Office/Access MVP>

Is there any general guildlines for preventing getting this message,
granting
that I do have a very complex query?

I have a half dozen or so queries in a series (one dependent on the
previous and so on) - My only cwork around I have have now is

having
to

copy
the results of a intermediate query to a tabel and then

referencing
that

table for the subsquent query.