If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Too Complex
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 |
#2
|
|||
|
|||
Query Too Complex
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. |
#3
|
|||
|
|||
Query Too Complex
" 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))); |
#4
|
|||
|
|||
Query Too Complex
On Jul 4, 11:39*am, cpsaltis
wrote: " 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)));- 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? |
#5
|
|||
|
|||
Query Too Complex
" wrote: On Jul 4, 11:39 am, cpsaltis wrote: " 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)));- 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? Yes, you misinterpreted the statement. I should have been clearer. The user enters into the form a selection parameter if they want "all records", "only customers over a year old", or "only customers less than a year old". Based upon their request the type value is set on the form. If they entered a type 1 then it tests the cu_install date. The queries works as designed when working with a local table. It only fails when bouncing against an ODBC table(s). I ran another test and had qAR-6 pull data from qAR-1 (eliminating all the conditionals) and it also works. I'd hate to do all the conditionals in VB and dynamically create the query. |
#6
|
|||
|
|||
Query Too Complex
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))); |
#7
|
|||
|
|||
Query Too Complex
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))); |
#8
|
|||
|
|||
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))); |
#9
|
|||
|
|||
Query Too Complex
Glad it works :~)
By the way, another difference between ODBC and JET optimisation is that ODBC can only handle one Left or Right Join per query. If you want to actually make lots of Left and Right joins run faster with ODBC, you have to use a pass-through query. Inner Joins are sent off to the ODBC server, but due to a limitation in ODBC SQL any more Left or Right joins are simulated by Jet by joining up the separate records returned by ODBC. This is because Left and Right joins were something of a new feature when Access was introduced, not well supported by other SQL databases. But this is just a note about optimisation :~) Left and Right joins don't make your query 'too complex' -- that message normally relates to the way the criteria are applied, as you have found. regards (david) "cpsaltis" wrote in message ... 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])))Fa lse) 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])))Fa lse) 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])))Fa lse) 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))); |
Thread Tools | |
Display Modes | |
|
|