Home   Fees/Services   Access Templates   Tutorials   Tutorial Downloads   Articles   Search   Contact  Privacy  Links
Query Is Too Complex
Not Enough Memory to Run Query
Home > Tutorials > Query Tutorials > Query Too Complex
 






Query Tutorials Index:
Too Complex Query
Aggregate Query
Not In Operator
Choose Function
Crosstab Query
Date Time Query
SQL Delete Query
External Table Query
Report Filter Query
Group By Query
Having Query Clause
Histogram Query
Access Append Query
Master Detail Update
Order By Query
SQL Parameter Query
Predicate Query
Self Join Query
Scalar SubQuery
Master Detail Query
Select Query
Access SQL Select Top
SQL Union Query
SQL Update Query
Custom Query Function

 

MultiSelect Parameter Forms Access Tutorial


Query Too Complex

Queries can become too complex in Microsoft Access database when  Access rewrites or restructures the where clause in a query.  When this rewrite occurs it is possible to prevent Access from writing the where clause by using the programming technique described below.



Since 2000 we have developed nearly 500 databases for clients throughout the world. See a sample of them at our recent clients page.

We also offer Microsoft SQL Server / Azure / Cloud database development through our programmer partnership with Jay McCormick and his team.


Here is a complex SQL query - not the where clause written in SQL view for a query used to select records based on several multi-select parameter lists.  Once Access re-writes this query it becomes too complex or the query runs out of memory.

WHERE ([m_projects].[project_ID] In (select project_Id from T_Projects) Or 0 In (select project_id from t_projects))
And
([m_project_bidders].[bidder_id] In (select bidder_id from t_bidders) Or 0 In (select bidder_id from t_bidders))
And
([m_projects].[project_type_ID] In (select project_type_Id from t_project_Types) Or 0 In (select project_type_id from t_project_types))
And
([m_project_items].[product_grp_id] In (select product_grp_id from t_prod_grps) Or 0 In (select product_grp_id from t_prod_grps))
And
([L_MFGs].[MFG_ID] In (select mfg_id from t_mfgs) Or 0 In (select mfg_id from t_mfgs))
And
([M_Projects].[Sales_Person_ID] In (select person_id from t_persons) Or 0 In (select person_id from t_persons))
And
([M_Projects].[Bid_Date]>=get_global('gbl_bid_Start_date')
And
[M_Projects].[Bid_Date]<=get_global('gbl_bid_end_date'));



Our Access Programmers work with clients all over the world through the Internet. See why we are the: the best Access programming company.

We also offer Microsoft SQL Server / Azure / Cloud database development through our programmer partnership with Jay McCormick and his team.


This query will run as is.  However, if you change to regular query design view then Access rewrites the SQL where clause to go into the criteria section of the query grid.  Below is just a sample of the part of the resulting too complex query and huge SQL Where clause:

complex SQL queries examples

If you try to save the query you get the Access error: Expression too Complex in Query Expression... blah blah blah'.  The query is too complex for Access to save or run now.

There are three workarounds to fix the problem 'query too complex' problem.  You likely don't know about the third method and that is the purpose of this query tutorial.

The first query too complex solution is to break up the query into a series of queries with each query performing a couple of the where clause elements.

The second method is the create and run the query from visual basic - however this may not always work and you can still get the too complex error message.  This method also adds to the complexity of the database.

The last method (hack), possibly invented by Blue Claw, is to use the Choose function to prevent Access from rewriting the SQL where clause.  Here is an example of the workaround using the choose function with a compressed version of the SQL where clause from the example above:

WHERE (((Choose(1,([m_projects].[project_ID] In (select project_Id from T_Projects) Or 0 In (select project_id from t_projects)) And ([m_project_bidders].[bidder_id] In (select bidder_id from t_bidders) Or 0 In (select bidder_id from t_bidders)) And ([m_projects].[project_type_ID] In (select project_type_Id from t_project_Types) Or 0 In (select project_type_id from t_project_types)) And ([m_project_items].[product_grp_id] In (select product_grp_id from t_prod_grps) Or 0 In (select product_grp_id from t_prod_grps)) And ([L_MFGs].[MFG_ID] In (select mfg_id from t_mfgs) Or 0 In (select mfg_id from t_mfgs)) And ([M_Projects].[Sales_Person_ID] In (select person_id from t_persons) Or 0 In (select person_id from t_persons)) And ([M_Projects].[Bid_Date]>=get_global('gbl_bid_Start_date') And [M_Projects].[Bid_Date]<=get_global('gbl_bid_end_date'))))<>False));
 

Note the Choose command in the query.  Also note that we have hard coded a '1' so that the choose command only uses the first (and only) option.  Choose(1,mywherecode)

The great thing about using this solution to the query too complex problem is that you can view the query in regular design view and Access won't change it all around:

complex SQL queries examples

Now you can add some additional SQL where clause elements without running into the complex SQL queries problems.

Another benefit of using the choose function in this way is that if you can program directly in SQL you'll be able to create a complex query in 1/10th the time compared to programming the query in the standard grid design view.   And, coming back later to change or add a where clause element is simple and only takes a minute - otherwise it actually may be too complex for a programmer who is not familiar with the query.







A Blue Claw Software Design Template:

Customer Order Fullfillment Database
 









A Blue Claw Database Design Article:

Microsoft Access On The Internet
 









Blue Claw Database Design Downloadable Tutorial:
Inventory Calculations Download ( Single User)  








Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365
 


Contact Information
Copyright 2000-2017 Blue Claw Database Design
Development in Microsoft Access, Microsoft SQL/Server and Azure

See our SQL/Server Development and Access Migration Tutorials