Solved

Query Too Complex?

Posted on 2011-02-16
5
423 Views
Last Modified: 2012-05-11
Hello I have the following query in a Microsoft Access database:

SELECT Count(*) AS Total_Record, Sum(IIf([Reprint Edition] Like 'get*',1,0)) AS Eligible, Sum(IIf([Reprint Edition] Like 'get*' And [Reviewed Item] Like 'exc*',1,0)) AS FT_Excluded, Sum(IIf([Reprint Edition] Like 'dng*',1,0)) AS Excluded, Sum(IIf([Reviewed Item] Like 'inc*',1,0)) AS FT_Included, Sum(IIf([Reprint Edition] Is Null,1,0)) AS RE_Null, Sum(IIf([Notes] Is Null,1,0)) AS Notes_Null, Sum(IIf([Reprint Edition] Like UNC,1,0)) AS RE_UNC

After adding "Sum(IIf([Reprint Edition] Like UNC,1,0)) AS RE_UNC"
I am getting the error that the 'Query Is Too Complex'.  I'm not really sure what this means nor do I know how to fix it.  Can anyone help?

Bevo
0
Comment
Question by:Bevos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 350 total points
ID: 34910311
http://allenbrowne.com/subquery-02.html
Error: "Query too complex"
JET gives this generic message any time it cannot pinpoint what's wrong. The cause can be wrong delimiters, misinterpreted data types, mismatched brackets, using reserved words as table or field names or aliases, confusion from Name AutoCorrect, or a host of other possibilities.

Is UNC really a column, or did you mean to quote it (as a string) in "Sum(IIf([Reprint Edition] Like UNC,1,0)) AS RE_UNC" ?
0
 

Author Comment

by:Bevos
ID: 34910392
Hi cyberkiwi, unc is field value for [Reprint Edition].  I tried to change it to "Sum(IIf([Reprint Edition] Like 'UNC*',1,0)) AS RE_UNC;" but that does not work as well.
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 150 total points
ID: 34910421
It would help to see the complete SQL statement.

Is there only one table or query in the FROM clause?

0
 

Author Comment

by:Bevos
ID: 34910481
Haha, I'm an idiot. I didn't include the FROM cause.  I'm gonna give you both some points and my God have mercy on my soul.

Bevo
0
 
LVL 21
ID: 34910501
Glad we could assist.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question