Query Too Complex

[DH]

When opening a form tied to a query some users receive the "Query Too Complex" error, while other users do not and the form opens okay.

Any reasons why some users may receive the error and other don't? I cannot re-create the error on my machine.

Thanks,

DH

 

[dhookom]

This happens. When it does and you want someone to help trouble-shoot then consider posting the SQL view.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[DH]

For some reason I cannot re-create the "Query Too Complex" on my machine or several others I have tested on. The query seems to work fine on some machines and not others. Even running fine on a Windows 2000 730 MHZ 256 MB RAM machine.

Here is the SQL View of the query:

SELECT nz([totalbuyerfees])+nz([downpaymentdollars]) AS amtneedesatclosing, [totalmtgcosts]-[totalsellercontributions] AS totalbuyerfees, nz([sellerescrowstartup])+nz([sellerinteriminterest])+nz([selleroriginationpoints])+nz([sellerdiscountpoints])+nz([sellerbrokerfee])+nz([sellerlenderfee])+nz([sellermiscfee])+nz([sellerrealtorfee])+nz([sellertitlefees]) AS totalsellercontributions, ([finalloanamount]*([rate]/100))/365*[interiminterestdays] AS interiminterestdollars, [finalloanamount]*[originationpoints]/100 AS originationdollars, [finalloanamount]*[discountpoints]/100 AS discountdollars, [finalloanamount]*[brokerfee]/100 AS brokerdollars, nz([escrowstartup])+nz([originationdollars])+nz([discountdollars])+nz([brokerdollars])+nz([applicationfee])+nz([miscfee])+nz([realtorfee])+nz([totaltitlefees])+nz([interiminterestdollars])+nz([underwritingfee])+nz([docprepfeelender])+nz([creditreportfee])+nz([floodcertfee])+nz([taxservicefee])+nz([appraisalfee])+nz([pointscreditdollars]) AS totalmtgcosts, nz((([totalmonthlyincome])*nz([maxfrontendqualifyingratio])/100)) AS maxpipmtallowed, nz([totalmonthlyincome])*(nz([maxbackendqualifyingratio])/100)-nz([totalliabilitypmts]) AS maxpitipmtallowed, IIf([pipayment] And [totalmonthlyincome] Is Not Null And [totalmonthlyincome]>0,([pipayment]+((nz([yearlypropertytaxes])/12)+(nz([yearlyhomeownersinsurance])/12)+nz([yearlypmiamount]))+nz([hoadues])+nz([subfinancingpayment]))/[totalmonthlyincome]) AS finalfrontendratio, IIf([totalmonthlyincome] And [totalliabilitypmts] Is Not Null And [totalmonthlyincome]>0,nz([totalliabilitypmts])+nz([pitipayment])+nz([hoadues])+nz([subfinancingpayment]))/nz([totalmonthlyincome]) AS finalbackendratio, IIf([liability1dontinclude]=-1,0,nz([liabilitybalance1]))+IIf([liability2dontinclude]=-1,0,nz([liabilitybalance2]))+IIf([liability3dontinclude]=-1,0,nz([liabilitybalance3]))+nz([LiabilityBalance4])+nz([LiabilityBalance5])+nz([LiabilityBalance6])+nz([LiabilityBalance7])+nz([LiabilityBalance8])+nz([LiabilityBalance9])+nz([LiabilityBalance10])+nz([LiabilityBalance11])+nz([LiabilityBalance12])+nz([LiabilityBalance13])+nz([LiabilityBalance14])+nz([LiabilityBalance15])+nz([LiabilityBalance16])+nz([LiabilityBalance17])+nz([LiabilityBalance18])+nz([LiabilityBalance19])+nz([LiabilityBalance20]) AS totalliabilitybalances, IIf([liability1dontinclude]=-1,0,nz([liabilitypayment1]))+IIf([liability2dontinclude]=-1,0,nz([liabilitypayment2]))+IIf([liability3dontinclude]=-1,0,nz([liabilitypayment3]))+nz([LiabilityPayment4])+nz([LiabilityPayment5])+nz([LiabilityPayment6])+nz([LiabilityPayment7])+nz([LiabilityPayment8])+nz([LiabilityPayment9])+nz([LiabilityPayment10])+nz([LiabilityPayment11])+nz([LiabilityPayment12])+nz([LiabilityPayment13])+nz([LiabilityPayment14])+nz([LiabilityPayment15])+nz([LiabilityPayment16])+nz([LiabilityPayment17])+nz([LiabilityPayment18])+nz([LiabilityPayment19])+nz([LiabilityPayment20])+nz([netrentalloss])-nz([total]) AS totalliabilitypmts, IIf([finalloanamount] And [purchaseprice/appraisedvalue] Is Not Null,(nz([purchaseprice/appraisedvalue])-nz([downpaymentdollars]))/[purchaseprice/appraisedvalue]) AS FinalLTV, (nz([pipayment])+(nz([yearlypropertytaxes])/12)+(nz([yearlyhomeownersinsurance])/12)+nz([yearlypmiamount])) AS pitipayment, IIf([rate] And [term] And [finalloanamount] Is Not Null,IIf([intonlypmt]=-1,nz([finalloanamount])*((nz([rate])/100)/12),Pmt(([rate]/100/12),([term]*12),-([finalloanamount])))) AS pipayment, IIf([chkfha]=-1,((nz([purchaseprice/appraisedvalue])-nz([downpaymentdollars]))*0.015)+nz([purchaseprice/appraisedvalue])-nz([downpaymentdollars]),nz([purchaseprice/appraisedvalue])-nz([downpaymentdollars])) AS finalloanamount, nz([purchaseprice/appraisedvalue])*nz([downpayment])/100 AS downpaymentdollars, IIf([netrentalincome]>0,[netrentalincome],0) AS finalnetrentalincome, (nz([grossrentalincome])*nz([vacancyfactor]/100))-nz([total]) AS netrentalincome, IIf([netrentalincome]<0,(-[netrentalincome])) AS netrentalloss, IIf([liabilitytype1]="rental mortgage",[liabilitypayment1]) AS rentalpayment1, IIf([liabilitytype2]="rental mortgage",[liabilitypayment2]) AS rentalpayment2, IIf([liabilitytype3]="rental mortgage",[liabilitypayment3]) AS rentalpayment3, IIf([liabilitytype4]="rental mortgage",[liabilitypayment4]) AS rentalpayment4, IIf([liabilitytype5]="rental mortgage",[liabilitypayment5]) AS rentalpayment5, IIf([liabilitytype6]="rental mortgage",[liabilitypayment6]) AS rentalpayment6, IIf([liabilitytype7]="rental mortgage",[liabilitypayment7]) AS rentalpayment7, IIf([liabilitytype8]="rental mortgage",[liabilitypayment8]) AS rentalpayment8, IIf([liabilitytype9]="rental mortgage",[liabilitypayment9]) AS rentalpayment9, IIf([liabilitytype10]="rental mortgage",[liabilitypayment10]) AS rentalpayment10, IIf([liabilitytype11]="rental mortgage",[liabilitypayment11]) AS rentalpayment11, IIf([liabilitytype12]="rental mortgage",[liabilitypayment12]) AS rentalpayment12, IIf([liabilitytype13]="rental mortgage",[liabilitypayment13]) AS rentalpayment13, IIf([liabilitytype14]="rental mortgage",[liabilitypayment14]) AS rentalpayment14, IIf([liabilitytype15]="rental mortgage",[liabilitypayment15]) AS rentalpayment15, IIf([liabilitytype16]="rental mortgage",[liabilitypayment16]) AS rentalpayment16, IIf([liabilitytype17]="rental mortgage",[liabilitypayment17]) AS rentalpayment17, IIf([liabilitytype18]="rental mortgage",[liabilitypayment18]) AS rentalpayment18, IIf([liabilitytype19]="rental mortgage",[liabilitypayment19]) AS rentalpayment19, IIf([liabilitytype20]="rental mortgage",[liabilitypayment20]) AS rentalpayment20, nz([rentalpayment1])+nz([rentalpayment2])+nz([rentalpayment3])+nz([rentalpayment4])+nz([rentalpayment5])+nz([rentalpayment6])+nz([rentalpayment7])+nz([rentalpayment8])+nz([rentalpayment9])+nz([rentalpayment10])+nz([rentalpayment11])+nz([rentalpayment12])+nz([rentalpayment13])+nz([rentalpayment14])+nz([rentalpayment15])+nz([rentalpayment16])+nz([rentalpayment17])+nz([rentalpayment18])+nz([rentalpayment19])+nz([rentalpayment20]) AS total, [Title Fees Form Query].TotalTitleFees, LoanTerms.DownPayment, Fees.ApplicationFee, LoanTerms.VacancyFactor, SubjectPropertyInfo.[PurchasePrice/AppraisedValue], Liabilities.LiabilityName1, Liabilities.LiabilityBalance1, Liabilities.LiabilityPayment1, Liabilities.LiabilityType1, Liabilities.LiabilityName2, Liabilities.LiabilityBalance2, Liabilities.LiabilityPayment2, Liabilities.LiabilityType2, Liabilities.LiabilityName3, Liabilities.LiabilityBalance3, Liabilities.LiabilityPayment3, Liabilities.LiabilityType3, Liabilities.LiabilityName4, Liabilities.LiabilityBalance4, Liabilities.LiabilityPayment4, Liabilities.LiabilityType4, Liabilities.LiabilityName5, Liabilities.LiabilityBalance5, Liabilities.LiabilityPayment5, Liabilities.LiabilityType5, Liabilities.LiabilityName6, Liabilities.LiabilityBalance6, Liabilities.LiabilityPayment6, Liabilities.LiabilityType6, Liabilities.LiabilityName7, Liabilities.LiabilityBalance7, Liabilities.LiabilityPayment7, Liabilities.LiabilityType7, Liabilities.LiabilityName8, Liabilities.LiabilityBalance8, Liabilities.LiabilityPayment8, Liabilities.LiabilityType8, Liabilities.LiabilityName9, Liabilities.LiabilityBalance9, Liabilities.LiabilityPayment9, Liabilities.LiabilityType9, Liabilities.LiabilityName10, Liabilities.LiabilityBalance10, Liabilities.LiabilityPayment10, Liabilities.LiabilityType10, Liabilities.LiabilityName11, Liabilities.LiabilityBalance11, Liabilities.LiabilityPayment11, Liabilities.LiabilityType11, Liabilities.LiabilityName12, Liabilities.LiabilityBalance12, Liabilities.LiabilityPayment12, Liabilities.LiabilityType12, Liabilities.LiabilityName13, Liabilities.LiabilityBalance13, Liabilities.LiabilityPayment13, Liabilities.LiabilityType13, Liabilities.LiabilityName14, Liabilities.LiabilityBalance14, Liabilities.LiabilityPayment14, BorrowerInfo.BorrowerID, Liabilities.LiabilityType14, Liabilities.LiabilityName15, Liabilities.LiabilityBalance15, Liabilities.LiabilityPayment15, Liabilities.LiabilityType15, Liabilities.LiabilityName16, Liabilities.LiabilityBalance16, Liabilities.LiabilityPayment16, Liabilities.LiabilityType16, Liabilities.LiabilityName17, Liabilities.LiabilityBalance17, Liabilities.LiabilityPayment17, Liabilities.LiabilityType17, Liabilities.LiabilityName18, Liabilities.LiabilityBalance18, Liabilities.LiabilityPayment18, Liabilities.LiabilityType18, Liabilities.LiabilityName19, Liabilities.LiabilityBalance19, Liabilities.LiabilityPayment19, Liabilities.LiabilityType19, SubjectPropertyInfo.HOADues, SubjectPropertyInfo.YearlyPropertyTaxes, SubjectPropertyInfo.YearlyHomeownersInsurance, SubjectPropertyInfo.PropertyType, SubjectPropertyInfo.SubjectPropertyAddress, SubjectPropertyInfo.SubjectPropertyCity, SubjectPropertyInfo.StateCodes, SubjectPropertyInfo.SubjectPropertyZip, Lenders.RateSheetDate, Lenders.QualifyingProgram, LoanType.Escrowing, LoanType.Use, LoanType.IsCurrentMtgPITI, Fees.InterimInterestDays, LoanType.MaxFrontEndQualifyingRatio, LoanType.MaxBackEndQualifyingRatio, Fees.EscrowStartup, Fees.OriginationPoints, Fees.DiscountPoints, Fees.BrokerFee, Fees.MiscFee, Fees.AppraisalFee, Fees.Sellerescrowstartup, Fees.SellerInterimInterest, Fees.SellerOriginationPoints, Fees.SellerDiscountPoints, Fees.SellerBrokerFee, Fees.SellerTitleFees, LoanTerms.IsRateLocked, LoanTerms.Rate, LoanTerms.Term, LoanTerms.RateLockDate, LoanTerms.RateLockExpirationDate, LoanTerms.YearlyPMIAmount, BorrowerInfo.BorrowerName, BorrowerInfo.CoBorrowerName, BorrowerInfo.CurrentAddress, BorrowerInfo.CurrentCity, BorrowerInfo.StateCodes, BorrowerInfo.CurrentZipCode, BorrowerInfo.BorrowerHomeTelephone, IIf([netrentalincome]>0,nz([borrowermonthlyincome])+nz([coborrowermonthlyincome])+nz([borrowerotherincome])+nz([coborrowerotherincome])+nz([netrentalincome]),nz([borrowermonthlyincome])+nz([coborrowermonthlyincome])+nz([borrowerotherincome])+nz([coborrowerotherincome])) AS totalmonthlyincome, BorrowerInfo.BorrowerMonthlyIncome, BorrowerInfo.CoBorrowerMonthlyIncome, BorrowerInfo.GrossRentalIncome, BorrowerInfo.BorrowerOtherIncome, BorrowerInfo.CoBorrowerOtherIncome, [maxfrontendqualifyingratio]/100 AS maxfrontendratiofactor, LoanTerms.RateType, CCur(IIf([maxpipmtallowed]<[maxpitipmtallowed],[maxpipmtallowed],[maxpitipmtallowed])) AS finalmaxpiti, [finalmaxpiti]-(nz([yearlypropertytaxes])/12)-(nz([yearlyhomeownersinsurance])/12)-nz([yearlypmiamount])-nz([hoadues]) AS finalmaxpi, ProgramMaintenance.TitleSplashOn, LoanTerms.interiminterestdayspurchase, LoanTerms.interiminterstdayspurchasedollars, Fees.purchasetotalclosingcosts, Fees.totalcostspaidbyseller, Fees.AppraisalFee, Fees.UnderwritingFee, Fees.Floodcertfee, Fees.Creditreportfee, Fees.taxservicefee, Fees.docprepfeelender, LoanTerms.RateSheet, LoanTerms.Escrow, LoanTerms.LockDays, LoanTerms.RateAdj1, LoanTerms.RateAdj1Desc, LoanTerms.RateAdj2, LoanTerms.RateAdj2Desc, LoanTerms.RateAdj3, LoanTerms.RateAdj3Desc, LoanTerms.RateAdj4, LoanTerms.RateAdj4Desc, LoanTerms.SRP, LoanTerms.RE, LoanTerms.BaseRate, LoanType.ProgramCode, nz([baserate])+nz([re])+nz([srp])+nz([rateadj1])+nz([rateadj2])+nz([rateadj3])+nz([rateadj4])+nz([pointscredit])+nz([discountpoints]) AS WholesalePrice, LoanTerms.Escrowmonthstaxes, LoanTerms.EscrowmonthsHOI, nz([totalbuyerfees])+nz([downpaymentdollars])-nz([cashdeposittowardpurchasevalue]) AS amtneedesatclosinglessdeposit, BorrowerInfo.cashdeposittowardpurchasevalue, ProgramMaintenance.CompanyMailingAddress, [companycity] & ", " & [programmaintenance.statecodes] & ", " & [companyzipcode] AS companycitystatezip, Liabilities.liability1dontinclude, Liabilities.liability2dontinclude, Liabilities.liability3dontinclude, LoanTerms.PointsCredit, LoanTerms.pointscreditdollars, Liabilities.TotalofPmts, Liabilities.TotalMonthlyIncome1, LoanTerms.pointscreditdollars, SubjectPropertyInfo.subordinatefinancing, IIf([finalloanamount] And [purchaseprice/appraisedvalue] Is Not Null,(nz([finalloanamount])+nz([subordinatefinancing]))/nz([purchaseprice/appraisedvalue])*100) AS cltv, Liabilities.FrontEndDebt, [Title Fees Form Query].totalsellerpaidtitlefees, LoanType.LenderCaseNumber, SubjectPropertyInfo.appraisalwaiver, Fees.SellerMiscFee, [Lead Turndowns].[BM Letter Mailed], LoanTerms.subrate, LoanTerms.subterm, LoanTerms.subintonly, LoanTerms.issubheloc, LoanTerms.subfixedadj, LoanTerms.subfinancingprogram, IIf([subintonly]=-1,[subordinatefinancing]*[subrate]/100/12,IIf([subrate] And [subterm] And [subordinatefinancing] Is Not Null,(Pmt(([subRate]/100/12),([subTerm]*12),-([subordinatefinancing]))))) AS subfinancingpayment, LoanTerms.armtype, LoanTerms.subarmtype, Lenders.internalrefinance, SubjectPropertyInfo.StateCodes AS propstate, LoanTerms.intonlypmt, LoanTerms.chkfha, Fees.miscfeedesc
FROM ProgramMaintenance, ((((((((((BorrowerInfo INNER JOIN Fees ON BorrowerInfo.BorrowerID = Fees.BorrowerID) INNER JOIN Lenders ON BorrowerInfo.BorrowerID = Lenders.BorrowerID) INNER JOIN Liabilities ON BorrowerInfo.BorrowerID = Liabilities.BorrowerID) INNER JOIN LoanTerms ON BorrowerInfo.BorrowerID = LoanTerms.BorrowerID) INNER JOIN LoanType ON BorrowerInfo.BorrowerID = LoanType.BorrowerID) INNER JOIN SubjectPropertyInfo ON BorrowerInfo.BorrowerID = SubjectPropertyInfo.BorrowerID) INNER JOIN YieldSpread ON BorrowerInfo.BorrowerID = YieldSpread.BorrowerID) INNER JOIN [Active Borrower] ON BorrowerInfo.BorrowerID = [Active Borrower].[Borrower ID]) INNER JOIN [Title Fees Form Query] ON BorrowerInfo.BorrowerID = [Title Fees Form Query].BorrowerID) INNER JOIN LiabilityContactInfo ON [Active Borrower].[Borrower ID] = LiabilityContactInfo.BorrowerID) INNER JOIN [Lead Turndowns] ON (BorrowerInfo.BorrowerID = [Lead Turndowns].BorrowerID) AND ([Active Borrower].[Borrower ID] = [Lead Turndowns].BorrowerID);

 

[dhookom]

Holy cow! That's some SQL! I assume you don't have much control over that table structure since it seems to be severely un-normalized.
-I never use Nz() without providing the "if null" value
nz([purchaseprice/appraisedvalue],0)
-If this query is supplying records for a report, you could easily move some calculations to the report
-There may be some differences of the JET versions between your computers.

I have seen queries that work one day on a PC and are too complex the next day.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[DH]

Holy cow is right. This query is left over from when I first started working with Access now I know better.

I will check the JET versions and see if there are any differences.

Thanks,


DH

 

[Clipper2000]

Hi,

For what it's worth, here's my sugestion:

Ditch this query and rewrite (code) it properly in a VBA module, with plenty of comments and explanations. Then use the DAO class to create the query for you by running the module.

The difference is this approach make debugging a lot easier few years down the road.

 

[JonFer]

Many times the problem is caused by a Null value in an unexpected field. In this situation, however, it may be that the query really is too complex.

John

http://www.interautomateconsulting.com

 

[OhioSteve]

You have a great many fields with names like [fieldName]+[integer]. For example, "liabilityAmount7". That very strongly suggests that your database is not normalized. My advice would be to normalize the database. We can help you to normalize it, and there are many books about relational db design. Normalization will reduce the query length to a small fraction of its current size, and it will solve many other problems.

After you normalize, you can decide how to proceed with this task. As dhookum said, you may need to write a VB program. However, I would guess that you won't need to do that if you fix the db structure first.

 

[lespaul]

and even if you can't change the structure, you can write a query that normalizes the data and use that query as the source for this query.

If you want to tell us about the structure of the table, we can help in designing a query to normalize it. In the meantime you should read Fundamentals of Relational Database Design



Leslie