MS Access Query: define a field displays “query is too complex” using * is Ok, though

Tag: ms-access-2007 Author: l270027891 Date: 2012-12-19

I'm trying to fix an MS Access Query that has begun failing. It's a system built by someone else who left a couple years ago, so I'm working backwards to understand it. The part that's failing is a query that joins two other sub queries.

If I run a 'SELECT *' on the query joins, it returns data without any problems. However, as soon as I try to define a specific field, it fails with the unhelpful message "Query is too complex".

So, this returns data no problem:

SELECT * FROM 530a_WIPIncomeShareByResource 
INNER JOIN 510_AllInvoicesCreditsInvWIP 
ON [530a_WIPIncomeShareByResource].WIPDocApplicable 
     = [510_AllInvoicesCreditsInvWIP].No_
WITH OWNERACCESS OPTION;

However, if I define any field from either of the two queries in the join, then it errors, giving me the "Query is too complex" error. So, this fails, for example:

SELECT [510_AllInvoicesCreditsInvWIP].Status 
FROM 530a_WIPIncomeShareByResource 
INNER JOIN 510_AllInvoicesCreditsInvWIP 
ON [530a_WIPIncomeShareByResource].WIPDocApplicable 
     = [510_AllInvoicesCreditsInvWIP].No_ 
WITH OWNERACCESS OPTION;

I've checked the two subqueries referenced in the join (530a_WIPIncomeShareByResource and 510_AllInvoicesCreditsInvWIP) respectively, and these both execute fine and return data. Joining the two doesn't, however.

I'm anything but experienced with Access so would appreciate any pointers whatsoever!


Hi this is the code for the sub queries:

530a_WIPIncomeShareByResource:

SELECT [502_WIP_DocApplicable].No_ AS [Resource No_], 
[502_WIP_DocApplicable].[Posting Date], 
[502_WIP_DocApplicable].WIPDocApplicable, 
[502_WIP_DocApplicable].WIPOpen, 
Sum(IIf([Work Type code]<>"WRITEOFF" 
        And [Type]<>3 
        And [Work Type code]<>"DISBURSE" 
        And [Work Type code]<>"DIRRESP" 
        And [Work Type code]<>"TAXJ"
        And [Work Type code]<>"INVMON" 
        And [Work Type code]<>"CUST"
        And [NAVTCIWORKTYPE-FEETYPE.Disbursement]<>1,[WIPValue],0)) AS QualWIP, 
Sum(IIf([Work Type code]="WRITEOFF",[WIPValue],0)) AS NonQualWOWIP, 
Sum(IIf([Work Type code]="DISBURSE" 
        Or [Work Type code]="XTNLPERCEN"
        Or [Type]=3 
        Or [NAVTCIWORKTYPE-FEETYPE.Disbursement]=1,[WIPValue],0)) AS DISBURSE, 
Sum(IIf([Work Type code]="DIRRESP" 
        Or [Work Type code]="TAXJ" 
        Or [Work Type code]="INVMON"
        Or [Work Type code]="CUST",[WIPValue],0)) AS FIXEDFEE,
Sum([502_WIP_DocApplicable].WIPValue) AS TotalWIP
FROM [NAVTCIWORKTYPE-FEETYPE] 
INNER JOIN 502_WIP_DocApplicable 
ON [NAVTCIWORKTYPE-FEETYPE].Code = [502_WIP_DocApplicable].[Work Type Code]
GROUP BY [502_WIP_DocApplicable].No_, 
         [502_WIP_DocApplicable].[Posting Date],
         [502_WIP_DocApplicable].WIPDocApplicable, 
         [502_WIP_DocApplicable].WIPOpen
WITH OWNERACCESS OPTION;

and the second:

510_AllInvoicesCreditsInvWIP

SELECT [510_AllInvoicesCredits].[Job No_], 
[510_AllInvoicesCredits].No_, 
[510_AllInvoicesCredits].[Invoice Date1] AS [Invoice Date],  
[510_AllInvoicesCredits].Status, 
[510_AllInvoicesCredits].[Invoice No_], 
[510_AllInvoicesCredits].REVERSED, 
[510_AllInvoicesCredits].[Reversal Document No_], 
[510_AllInvoicesCredits].[Reversal Document Date], 
[510_AllInvoicesCredits].Reference, 
IIf([DocType]="Credit",[TotalBill],([TotalBill]-[FTRaised]+(-
    (IIf(IsNull([FTWO]),0,
     IIf([TotalBill]<=-0.01,-[FTWO],[FTWO]))))-
    (IIf([TotalBill]<0,-[DISBURSE],[DISBURSE]))-
    (IIf([TotalBill]<0,-[FIXEDFEE],[FIXEDFEE]))-
    (IIf([TotalBill]<0,-[XTNLPERCENSHARE],[XTNLPERCENSHARE]))-
    (IIf([TotalBill]<0,-[FIXEDFEESHARE],[FIXEDFEESHARE])))) AS IncomeToAlloc, 
[510_AllInvoicesCredits].TotalBill, 
[510_AllInvoicesCredits].XTNLPERCENSHARE, 
[510_AllInvoicesCredits].FIXEDFEESHARE, 
[520a_WIPAnalysis1_WIP].QualWIP, 
[520a_WIPAnalysis1_WIP].NonQualWOWIP, 
[520a_WIPAnalysis1_WIP].DISBURSE, 
[520a_WIPAnalysis1_WIP].TotalWIP, 
[510_AllInvoicesCredits].FTRaised AS FTR, 
IIf(IsNull([FTWO]),0,IIf([TotalBill]<0,-[FTWO],[FTWO])) AS FTWO1, 
[520a_WIPAnalysis1_WIP].GenCFWIPUsed, 
[510_AllInvoicesCredits].GenCF AS GenCFRaised, 
[510_AllInvoicesCredits].WOPROVRaised AS WOPROVR, 
[520b_WIPAnalysis2_FT].WOPROVWO AS WOPROVUsed, 
[520a_WIPAnalysis1_WIP].FIXEDFEE, 
[510_AllInvoicesCredits].DocType, 
[510_AllInvoicesCredits].Doc
FROM (510_AllInvoicesCredits LEFT JOIN 520a_WIPAnalysis1_WIP 
ON [510_AllInvoicesCredits].No_ = [520a_WIPAnalysis1_WIP].WIPDocApplicable) 
LEFT JOIN 520b_WIPAnalysis2_FT 
ON [510_AllInvoicesCredits].No_ = [520b_WIPAnalysis2_FT].FTDocApplicable
WHERE ((([510_AllInvoicesCredits].REVERSED)<>1))
ORDER BY [510_AllInvoicesCredits].[Invoice Date1]
WITH OWNERACCESS OPTION;

This whole Access system is layer upon layer upon layer of queries. A real headache to decipher! the above do run just fine, however.

Please post the sql for the two subqueries. Are you working with a multi-user secure work-group?
So you really have a secure work group? That is, do you have to log-in using an *.mdw to use the back-end database? If the file is an *.accdb, you do not have a secure work-group, so WITH OWNERACCESS OPTION is irrelevant.
Just looking at that, and reading your comment, it seems that these subqueries are also queries of queries. Is that correct? If so, I suggest you go back to the tables and get back here with what you want to find out from the tables, or at the very least, get [510_AllInvoicesCreditsInvWIP].Status from [510_AllInvoicesCredits], which seems to be where it lives. As you say, the above is truly nightmarish.
Hi all, thanks for the responses. Yes - everything is a query on a query on a query... In some areas it's 8 levels deep before it gets to an actual table which really brings a tear to the eye! After some more digging into it, I've found that the original database was created in Access 2003, and using Access 2003 to run it instead of 2007 results in the previously-failing code now executing. Completely baffling and I can only assume it's some kind of bug as it's not exactly complicated SQL code and should execute on any version. Thank-you all for the assistance with this one!!