Re: Query Too Complex

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

On Jul 4, 11:39 am, cpsaltis <cpsal...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
"louisjohnphill...@xxxxxxxxx" wrote:
On Jul 4, 10:41 am, cpsaltis <cpsal...@xxxxxxxxxxxxxxxxxxxxxxxxx>
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)));- Hide quoted text -

- Show quoted text -

Am I misinterpreting this?

SELECT all rows from qAR-3 if the customer type is 0 or -1 without
restrictions based on install date.
Add to that any customer type 1 if installation was over a year ago.
Add to that any customer of type 2 with an installation date within
the last year.

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)));

This appears to be selecting from itself. Would that be causing the
nesting error?
.

Relevant Pages

  • Re: MS Access looks for .mdb rather than Progress schema
    ... # Same query previously worked in Progress 8.x using SQL89. ... This entry allows you to keep your existing code written with the ODBC ... BTW, if you put all of the connect information into the connect string, ... I also tried putting the whole thing in the connection string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Word 2000/2002 - Proper Mail Merge steps for ODBC?
    ... > I don't get the "Database has been placed in a state by ... Access runs the query and will prompt for any ... > my ODBC entry and click the "Configure" button, ... >>using OLEDB, it uses a more exclusive mode than it really ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Word 2000/2002 - Proper Mail Merge steps for ODBC?
    ... I was able to find the MS Query button and locate my ... in both is ODBC. ... >> be certain that I am using the OLEDB method? ... >from the list of possible connection options. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: MS Access looks for .mdb rather than Progress schema
    ... This is not passthrough query sql. ... and should be translated into ODBC SQL. ... BTW, if you put all of the connect information into the connect string, ... I also tried putting the whole thing in the connection string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Simple query executes fast but renders slow...
    ... After you restart Access and run your query, JET will write an extensive log ... "SQL Queries for Mere Mortals" ... >>Then I wrote queries and reports based on the local ... So, ODBC, right? ...
    (microsoft.public.access.queries)