Query Too Complex

[longhorn]

I have a query that is giving me the error that the query is too complex. I have a list box on a form where I am selecting parts and sending them to a report. I have gone back to the tables where this information is made all of the field sizes smaller. I am able to select 42 parts and when I try to do 43 or more I receive the error that my query is too complex. I need to be able to select more parts so if you could please tell me how to fix this problem that would be great

 

[GinaWhipp]

Welcome to the Forum!

Please post the SQL of the query for us to better see the problem.

 

[longhorn]

Thanks. I'm glad to be here

It's 90,088 characters and it said the limit is 25,000 so I don't know what you suggest I do

 

[GinaWhipp]

If the query is that long then *that* is the problem and the reason for the error message. To confirm, paste it into a Word document and upload that.

 

[longhorn]

If the length is the reason even if I shorten the names of the fields that might help a little bit but not that much. Is there a way to increase the size of a query?

 

[GinaWhipp]

There is no way to increase the size of the query. And, to be 100% sure is why I wanted to see the SQL of the query. Might be a way we can shorten it.

 

[longhorn]

it keeps saying invalid file when I try to upload the word document

 

[GinaWhipp]

Hmm, Forum not making it easy, try zipping it.

 

[longhorn]

There is the beginning of it. every time I add a new part it just adds another union and repeats the same thing

 

[GinaWhipp]

I'm thinking you might want to consider a Subreport or two, this really is too much for one query, see...
http://regina-whipp.com/blog/?p=325

That way you can break this query down to something more manageable and something it can actually run.

 

[longhorn]

Okay thank you very much. Also is it true for Microsoft Access 2010 that the query SQL limit is 64,000 characters?

 

[GinaWhipp]

Yes, it is... after that it will either start generating errors, crash or just won't run.

 

[CJ_London]

Bit puzzled. If the limit is 64,000 characters and Longhorn has 90,088 I struggle to believe the query is failing at that point - it would have failed much earlier unless he has just copied a query into the union which is in excess of 26000 characters - which seems out of kilter with it being the 43rd item.

You can get the 'query is to complex' error if you are trying to process a null value, wrong type of calculation (e.g. trying to multiply a numeric field and a text field) or number out of range (e.g. converting a double value to a single value when the value is to large) which implies an issue with the data.

Since this failed on the 43rd item, I would look at the data this item is generated from. Since you have a union query it is possible this part of the query will run OK on its own but comes into conflict with the rest of the union query (e.g. columns in wrong order or wrong type)

Pure speculation on my part, but from the size of the query I would also suspect that the database is not normalised - perhaps something like one table for each 'type' of part rather than one table for parts

 

[GinaWhipp]

@CJ_London

This...

I am able to select 42 parts and when I try to do 43 or more I receive the error that my query is too complex

...is what led me to beleive the query is hitting the mark. Even what he posted is only a little over 4,000 characters which is no where near the mark. BUT if you keep going then boom, you're there. Of course, whithout being to see the entire query, I too am guessing.

 

[CJ_London]

@Gina
Haven't actually seen the query - zipped or otherwise! seems to have been removed

My point is that if he has (for the sake of argument) 90,000 characters for 42 items, then based on this

every time I add a new part it just adds another union and repeats the same thing

each part of the query is about 2140 characters (ignoring differences in lengths of field/table names), so it should have failed after about 30 items.

 

[GinaWhipp]

@CJ

I hadn't even noticed it was removed... it was here. Hmm, nope, I didn't see that what I saw would fail based on adding even another single character becuase it would hit the mark, and mind you, I am still guess because the entire query was never posted only a portion.

I should mention from what I saw, the OP was trying to pull the 1 to many in one query with one report which why I suggested more queries and subreports. I guess if there is still a problem we'll be hearing from the OP again.

 

[vbaInet]

I wonder what longhorn's strategy is for including the selected parts into the query?? I think rather than using a numeric ID and the IN() clause, perhaps longhorn is using the full part name and concatenating it with the OR clause.

The other suggestion would be instead of using the SELECT statement in the report's record source, save that as a query and use the query name.
EDIT: Looks like Gina just suggested this.