View Single Post
  #8  
Old July 6th, 2008, 06:26 PM posted to microsoft.public.access.queries
cpsaltis
external usenet poster
 
Posts: 6
Default Query Too Complex


Opps, should have said separate line in Design View.

"cpsaltis" wrote:

David,

Thank you for the explanation.

I also ran across a post from Michel Walsh demonstrating an alternative to
the criteria [field] with separate line for [field] is null which gets
complicated with multiple fields, ie ([field1] AND [field2]) OR ([field1] AND
[field2] is null) OR ([field1] is null AND [field2]) OR ([field1] if null AND
[field2] is null) which allowed me to also reduce the number of sub queries.
Queries now work with ODBC link.

SELECT [qAR-1].*
FROM [qAR-1]
WHERE (((IIf(([Forms]![fARSelections]![BranchNum]) Is
Null,True,([cu_branch])=[Forms]![fARSelections]![BranchNum]))False) AND
((IIf(([Forms]![fARSelections]![MonthNum]) Is
Null,True,([Forms]![fARSelections]![MonthNum])=DatePart("m",[ph_date])))False)
AND (([Forms]![fARSelections]![CustType])=0 Or
([Forms]![fARSelections]![CustType])=-1)) OR
(((IIf(([Forms]![fARSelections]![BranchNum]) Is
Null,True,([cu_branch]=[Forms]![fARSelections]![BranchNum]))False) AND
((IIf(([Forms]![fARSelections]![MonthNum]) Is
Null,True,([Forms]![fARSelections]![MonthNum])=DatePart("m",[ph_date])))False)
AND (([Forms]![fARSelections]![CustType])=1) AND ([cu_install](Date()-365)))
OR (((IIf(([Forms]![fARSelections]![BranchNum]) Is
Null,True,([cu_branch]=[Forms]![fARSelections]![BranchNum]))False) AND
((IIf(([Forms]![fARSelections]![MonthNum]) Is
Null,True,([Forms]![fARSelections]![MonthNum])=DatePart("m",[ph_date])))False)
AND (([Forms]![fARSelections]![CustType])=2) AND
([cu_install]=(Date()-365)));


"david@epsomdotcomdotau" wrote:

Queries are optimised all the way through the stack,
so it is not 'more efficient' to put the selection at the
bottom of the stack -- it just makes the query more
complex to optimise for ODBC.

The internal rules for optimisation of an ODBC query
are arcane, unspecified and yes, different from the Jet
optimisation, because the Jet engine does call-backs
to get Date(), before getting the records from an MDB,
but the ODBC version applies that selection criteria in
Jet after getting all the records from ODBC first.

In any case, just re-write the query stack so that it is
different: any change changes the optimiser path, and
so can change if it considers the query too complex.

(david)


"cpsaltis" wrote in message
...


" wrote:

On Jul 4, 10:41 am, cpsaltis
wrote:
I have nested queries that when run on a local table runs correctly.

When I
switch the first query to use ODBC tables I get query too complex. If

I run
the individual queries (in the nest) they work all the way to the very

last
one (that the report uses). Other than the ODBC connection the

difference is
local table is single table, ODBC is two linked tables. I've see here

that
there is a 64k compiled limit to the size of the query. Is there a way

to
identify the size? Is there a different limit when using and ODBC

connection?

Thanks in advance

Can it be assumed that the ODBC connection is to a database that
supports views? View seen by Access through an ODBC connection appear
if they are tables.

Rather than worry about the limit of the size of a query, try breaking
the query into views that have performed most of the selection work.
Use this technic to simplify the complex query.


I don't quite understand your comments about views.

The reason for nesting, other than some calculations etc, is to limit
selections. The first 4 do most of the selction work. I've included the

2nd &
4th for example. The reason they are in different queries is to simplify.

SELECT [qAR-1].*
FROM [qAR-1]
WHERE ((([qAR-1].cu_branch)=[Forms]![fARSelections]![BranchNum]) AND
(([Forms]![fARSelections]![MonthNum])=DatePart("m",[ph_date]))) OR
((([qAR-1].cu_branch)=[Forms]![fARSelections]![BranchNum]) AND
(([Forms]![fARSelections]![MonthNum]) Is Null)) OR
((([Forms]![fARSelections]![MonthNum])=DatePart("m",[ph_date])) AND
(([Forms]![fARSelections]![BranchNum]) Is Null)) OR
((([Forms]![fARSelections]![MonthNum]) Is Null) AND
(([Forms]![fARSelections]![BranchNum]) Is Null));

SELECT [qAR-3].*
FROM [qAR-3]
WHERE ((([Forms]![fARSelections]![CustType])=0 Or
([Forms]![fARSelections]![CustType])=-1)) OR
((([Forms]![fARSelections]![CustType])=1) AND
(([qAR-3].cu_install)(Date()-365))) OR
((([Forms]![fARSelections]![CustType])=2) AND
(([qAR-3].cu_install)=(Date()-365)));