Solved

Query Too Complex?

Posted on 2011-02-16
5
426 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 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