Re: Query Too Complex

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

How many grouping levels do you need?
>From memory, the limit is about 9.

Is it failing with fewer than that?
If so, what data types are in use at each level?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Adam" <Adam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AFEC7D8E-0A68-4523-8F16-7F952948B899@xxxxxxxxxxxxxxxx
> Many thanks for this Allen, it is very useful.
>
> The biggest problem I have is the report not running when it is based
> directly on a table. The only solution I had to this was to remove some of
> the grouping levels, however this is by no means ideal.
>
> Do you know of anything I can do to make this report run with the number
> of
> grouping levels I want?
>
> Thanks again,
>
> Adam.
>
>
>
>
> "Allen Browne" wrote:
>
>> The 'Query too complex' message means that Access is not able to
>> interpret
>> how to run the query. (If the report is based directly on a table, that
>> could include the query that the report itself creates in order to
>> perform
>> the Sorting And Grouping you requested.)
>>
>> The functionality has not been reduced since A97, so the fact that the
>> same
>> thing worked there indicates that the query is not too complex.
>>
>> However, JET 4 (the query engine in Access 2000 and later) is certainly
>> poorer at understanding the data types of calculated query fields, and it
>> generates this error when it gets confused about how to apply criteria or
>> if
>> the types don't match. It is highly likely that this is the cause or the
>> problem with the UNION query, and probably the others as well.
>>
>> The solution is to explicitly typecast the calculated fields in the
>> query.
>> For details, see:
>> Calculated fields misinterpreted
>> at:
>> http://allenbrowne.com/ser-45.html
>>
>> The article should also encourage you to declare any parameters in your
>> queries. For example, if your OrderID field has this in the Criteria row:
>> [Forms].[Form1].[OrderID]
>> then choose Parameters on the Query menu (in query design), and in the
>> dialog enter:
>> [Forms].[Form1].[OrderID] Long
>>
>> For the UNION query, the data type is determined from the first SELECT
>> statement, so if you have:
>> SELECT ID, Null As Expr1 FROM ...
>> then the 2nd field will be intrepreted as text, regardless of the data
>> type
>> of the 2nd column in the 2nd SELECT. To avoid that, reverst the
>> statements,
>> typecast the field, or use this fudge to get it interpreted as a number:
>> SELECT ID, IIf(False, 0, Null) As Expr1 FROM ...
>>
>> "Adam" <Adam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:34BDB988-09D9-46A3-8682-A23A148CFD33@xxxxxxxxxxxxxxxx
>> >
>> > My organisation has recently upgraded from Access97 to Access2002 and
>> > many
>> > of the reports I built in Access97 no longer work. The error message
>> > 'Query
>> > too Complex' is always returned - even if the report is based on a
>> > table.
>> >
>> > I wonder if microsoft have reduced to number of functions reports can
>> > handle
>> > as these reports are quite complex, they have many levels and the
>> > controls
>> > can contain more than one function.
>> >
>> > When i reduce the number of controls on the report the report does run,
>> > however this not an ideal solution. I wondered if anyone else had
>> > encountered
>> > this problem and knew a work around. It seems that in terms of report
>> > complexity microsft have taken a step backward from 97 to 2002.
>> >
>> > I also have the same problem running a very simple report that is based
>> > on
>> > a
>> > union query.
>> >
>> > Any help would be greatly appriecated.
>> >
>> > Many thanks,
>> >
>> > Adam.


.

Relevant Pages

  • Re: Report Date Range Form -- Error
    ... That's probably because it is confused about the data types. ... Declare the parameters in the query. ... This indicates to JET to treat the values as dates. ... >I have a report - Customer Sales - with a Report Date Range Form to open ...
    (microsoft.public.access.forms)
  • Re: Query Too Complex
    ... These IIfstatements that are being summed in the group footers... ... into calculated query fields, and explicitly typecast the results so Access ... That should at least take the burden out of the grouping levels, ... make that easier for the report, and it may also help you identify the part ...
    (microsoft.public.access.reports)
  • Re: Query Too Complex
    ... >> the grouping levels, however this is by no means ideal. ... >> Do you know of anything I can do to make this report run with the number ... >>> how to run the query. ... >>> poorer at understanding the data types of calculated query fields, ...
    (microsoft.public.access.reports)
  • Re: Using a query to limit both date AND time range on a report
    ... One common example is that the data types are wrong. ... In query design choose Parameters on the Query menu. ... >I currently am using a query as the record source for a report that I>run ...
    (microsoft.public.access.queries)
  • Re: Lookup Column to Report
    ... It's a common practice to base a report on a query. ... submitted but there is usually more than one photographer. ... AGAINST using lookup data types in table definitions. ...
    (microsoft.public.access.tablesdbdesign)