Query is too complex

Hi, I am a relative amateur at this. I have 5 queries
each of which relies on the one before for a number.
query5 looks up a field in query4, query4 looks up a field
from query3...They all have an iif statement to verify the
value of that field. This iif statement generates the
value for the field for the next query. In query5, I got
a "Query is too complex" error. I tried to combine
queries 4 and 5 and still received the same error.

My thinking was to separate the queries in the first place
so that each one had a different function. I've seen
really long messy queries before and I was hoping to avoid
that. I don't know SQL or VBA so if you go there, be
gentle.

Thanks
Mike

There might be a better way of handling what you're trying
to do with the multiple querys. However, if you want to
keep your process the same, you might try changing query 3
or query 4 to a make table query, then using the table for
your subsequent query(s). This should eliminate the "too
complex" message.

-----Original Message-----
Hi, I am a relative amateur at this. I have 5 queries
each of which relies on the one before for a number.
query5 looks up a field in query4, query4 looks up a
field
from query3...They all have an iif statement to verify
the
value of that field. This iif statement generates the
value for the field for the next query. In query5, I got
a "Query is too complex" error. I tried to combine
queries 4 and 5 and still received the same error.

My thinking was to separate the queries in the first
place
so that each one had a different function. I've seen
really long messy queries before and I was hoping to
avoid
that. I don't know SQL or VBA so if you go there, be
gentle.

Thanks
Mike
.

Thanks Les, that was (is) my back up plan. I'd have a
macro delete the table, run query4 as a make table query,
then run query 5 from the new table.

I just keep thinking there is a better way. But I guess
learning SQL and/or VBA would be a start.

Thanks for your help.
Mike
-----Original Message-----
There might be a better way of handling what you're
trying
to do with the multiple querys. However, if you want to
keep your process the same, you might try changing query
3
or query 4 to a make table query, then using the table
for
your subsequent query(s). This should eliminate the "too
complex" message.

-----Original Message-----
Hi, I am a relative amateur at this. I have 5 queries
each of which relies on the one before for a number.
query5 looks up a field in query4, query4 looks up a
field
from query3...They all have an iif statement to verify
the
value of that field. This iif statement generates the
value for the field for the next query. In query5, I
got
a "Query is too complex" error. I tried to combine
queries 4 and 5 and still received the same error.

My thinking was to separate the queries in the first
place
so that each one had a different function. I've seen
really long messy queries before and I was hoping to
avoid
that. I don't know SQL or VBA so if you go there, be
gentle.

Thanks
Mike
.

.

On Thu, 27 May 2004 08:51:29 -0700, "Mike"
wrote:

Hi, I am a relative amateur at this. I have 5 queries
each of which relies on the one before for a number.
query5 looks up a field in query4, query4 looks up a field
from query3...They all have an iif statement to verify the
value of that field. This iif statement generates the
value for the field for the next query. In query5, I got
a "Query is too complex" error. I tried to combine
queries 4 and 5 and still received the same error.

My thinking was to separate the queries in the first place
so that each one had a different function. I've seen
really long messy queries before and I was hoping to avoid
that. I don't know SQL or VBA so if you go there, be
gentle.

I agree with Les - there's got to be a better way. IIF() and nested
queries can both be very inefficient! And if by "looks up" you mean
DLookUp, that's often even worse.

Could you perhaps gulping hard here open the queries in SQL view and
post them here, along with an explanation of the table structure and
what the query is intended to accomplish?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public