Query is too complex

[Mike]

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

 

[Les]

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.

 

[Guest]

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

 

[John Vinson]

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?