Query to Complex

[pazgb]

I am getting a query to complex error. I am basiclly Selecting a bunch of queries that calc different formulas so all the queries are a must. All the queries run just fine by themselfs. It's just when i design a report I need to add all the queries. I have been searching on the internet for help, but not much out their. I saw one site suggested to do a make-table query then run the report off of the table? is that possiable?

Please help
Michael

 

[yamafopa]

Sometimes I find it easier to do some of the calculating or calling for calculated values while the report is being formatted. It slowes things down a bit, but can make the data source issues easier. Only call for the base data with your recordsource query and then do the calculations on the report...

yamafopa!

 

[GingerR]

i have a lot of complex queries and yes i have done not a make-table but append and update queries. make-table queries bloat the db too quick. so, make a table that is formatted like the final table should be that is the source for the report. figure out which query/queries can be run to first APPEND into that table. then if there are other columns that need to be filled with calculations from other queries, make them into UPDATE queries. launch the report from a button on a form. first tho, in the button's OnClick event, do these things:

delete all data from the new table.
run the first append query.
run the update queries.
open the report.

ok? hope this helps.

g

 

[DanChard]

The "Query Too Complex" error can usually be got round just by adjusting your SQL slightly. It's difficult to explain exactly how this can be accomplished though without seeing the types of query you're trying to join together. How many of them are there and how long are they typically?

Something that usually helps me out is to paste all the individual queries into a single query, place each of the queries inside brackets and then treat each as a separate item in the select list. For example:

select (sql from query1), (sql from query2), (sql from query3)
from (some table);

The major drawback with this approach is that it relies on each of the queries being used to return a single value. Otherwise it will complain.

If you haven't got too many queries and they're not too long then paste them here and perhaps someone can do what you want with them without it being too complex for poor Access!

 

[TnGranny]

I get this error when trying to perform a 1) a compare in an IIF statement between a numeric and non-numeric field, 2) a calculation using a non-numeric field and 3) a selection based on dates. The offending field, which Access looks at as non-numeric (or "non-date&quot, always comes from another query.

Possibility 1
--------------
It's actually supposed a numeric field and isn't

To fix this, set the property of the offending field to a numeric data type in the query that creates/outputs it.

To find it, run your select queries individually and look for fields that should be numeric which are left justifield in the display.

Possibility 2
-------------
the query that is "too complex" has a compare or calculation using dates

To fix, set the property of all date fields output in queries and used later for calculations or compares to a valid date datatype. If it won't let you set the property to a date, formatting the field as a date usually works. Don't be fooled by the display of the date field when you run individually the select query that creates it .. it can look like a date but not be recognized as a date in subsequent queries.

Possibility 3
-------------
a legitimate non-numeric field (which happens to have a digit in it) that you don't have enclosed in quotes in an IIF statement or select criteria. Example, RecordType is a one-position text field and you have a query with "IIF(RecordType=1,..." Make that "IIF(RecordType="1",..." and it won't be too complex.

 

[Ed2020]

I've had this happen before and the QueryDef object has been corrupted.

Copying the SQL out of your query and pasting it into a new querydef has always done the trick for me......

Ed Metcalfe.

Please do not feed the trolls.....