Query is too complex

[Vensia]

Dear all,

I have a query and returns error "query is too complex" if I run it in
Access 2000. But the query runs well in Access 97 environment. Please guide
me to solve this problem.
Thanks.

Regards,
Vensia

 

[Vincent Johns]

Dear all,

I have a query and returns error "query is too complex" if I run it in
Access 2000. But the query runs well in Access 97 environment. Please guide
me to solve this problem.
Thanks.

Regards,
Vensia

It seems strange, since it worked in Access 97, but queries can
overwhelm Access at times. You might post your SQL here.

Can you easily split the Query into parts, such that the inner Query
does part of the calculation and the outer Query invokes it by name?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vensia]

The SQL statement is like this :

SELECT * FRM qryA
UNION
SELECT * FRM qryB

The query returns error but if I change the SQL statement like this

SELECT * FRM qryB
UNION
SELECT * FRM qryA

The query doesn't return error.

 

[Vincent Johns]

The SQL statement is like this :

SELECT * FRM qryA
UNION
SELECT * FRM qryB

The query returns error but if I change the SQL statement like this

SELECT * FRM qryB
UNION
SELECT * FRM qryA

The query doesn't return error.

I assume "FRM" was really "FROM" in the SQL.

The complexity is likely in one of the Queries [qryA] or [qryB].

One way to break it up, though it will clutter your database, is to
convert part of your Query to a Make-Table Query or an Append Query,
putting the results into a new Table that you can then delete after you
have finished using it.

In the case of your UNION, you could use a Make-Table Query out of
[qryA] and an Append Query out of [qryB]. An ordinary Select Query
could then return whatever you need from that new Table. If you want to
combine all these operations into a Macro, you could run them, using the
Macro, without any manual intervention.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.